I'm not a big fan of IDENTITY's, but here is some sample code.
You should post the "why" are you needing the return values of the multiple
inserts.
Here is a demo (its not a 100% match of your question) using the "output"
feature in TSQL.
SET NOCOUNT ON
GO
DROP Table dbo.Person
GO
DROP Table dbo.Dept
GO
Create Table dbo.Dept
(
DeptID int primary key IDENTITY ( 101 , 1 ) ,
DeptName varchar(12) not null
)
GO
ALTER TABLE dbo.Dept
ADD CONSTRAINT DEPT_DEPTNAME_UNIQUE_CONSTR UNIQUE (DeptName)
GO
Create Table dbo.Person
(
PersonID int primary key IDENTITY ( 1001 , 1 ) ,
DeptID int not null FOREIGN KEY (DeptID) REFERENCES dbo.Dept(DeptID),
SSN varchar(9) not null ,
LastName varchar(12) not null ,
FirstName varchar(12) not null
)
GO
ALTER TABLE dbo.Person
ADD CONSTRAINT PER_SSN_UNIQUE UNIQUE (SSN)
GO
IF OBJECT_ID('tempdb..#TempDept') IS NOT NULL
begin
drop table #TempDept
end
IF OBJECT_ID('tempdb..#TempPerson') IS NOT NULL
begin
drop table #TempPerson
end
IF OBJECT_ID('tempdb..#AuditTablePerson') IS NOT NULL
begin
drop table #AuditTablePerson
end
IF OBJECT_ID('tempdb..#AuditTableDept') IS NOT NULL
begin
drop table #AuditTableDept
end
Create Table #TempPerson
(
PersonID int default 0 , --<<We don't know this one
FakeDeptID int ,
SSN varchar(9) not null ,
LastName varchar(12) not null ,
FirstName varchar(12) not null
)
Create Table #AuditTablePerson
(
SSN varchar(9) not null ,
NewIdentityPersonID int not null
)
Create Table #AuditTableDept
(
DeptName varchar(12) not null ,
NewIdentityDeptID int not null
)
Create Table #TempDept
(
FakeDeptID int not null ,
DeptName varchar(12) not null
)
GO
/*
Notice with the "-111" (and -222) I have a Relationship between the Person
and Dept, but this value
isn't the actual DeptID in the database. We just use this -111 (and -222)
as a holder for the relationship
until the IDENTITIES are actually created.
*/
Insert into #TempDept ( FakeDeptID , DeptName ) values ( -111 ,
'Resources' )
Insert into #TempDept ( FakeDeptID , DeptName ) values ( -222 ,
'Janitorial' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName ) values
( -111 , '222222222', 'Person2' , 'Two' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName ) values
( -111 , '333333333', 'Person3' , 'Three' )
Insert into #TempPerson ( FakeDeptID , SSN , LastName , FirstName ) values
( -222 , '444444444', 'Person4' , 'Four' )
print '/#TempPerson/'
select * from #TempPerson
print '/#TempDept/'
select * from #TempDept
print '------------<'
Insert Into dbo.Dept (DeptName)
output inserted.DeptName , inserted.DeptID into #AuditTableDept ( DeptName ,
NewIdentityDeptID )
select DeptName from #TempDept td
where not exists ( select null from dbo.Dept innerDept where
innerDept.DeptName = td.DeptName )
print '/#AuditTableDept/'
select * from #AuditTableDept
/*
--Here is where we can get the actual DeptID (created from the IDENTITY) by
using the FakeDeptID relationship we created and
--banking on that the DeptName's are unique
*/
/*
insert into dbo.Person (DeptID , SSN , LastName , FirstName)
output inserted.SSN , inserted.PersonID into #AuditTablePerson ( SSN ,
NewIdentityPersonID )
select ad.NewIdentityDeptID , SSN , LastName , FirstName from #TempPerson tp
join #TempDept td on tp.FakeDeptID = td.FakeDeptID
join #AuditTableDept ad on ad.DeptName = td.DeptName
*/
insert into dbo.Person (DeptID , SSN , LastName , FirstName)
output inserted.SSN , inserted.PersonID into #AuditTablePerson ( SSN ,
NewIdentityPersonID )
select d.DeptID , SSN , LastName , FirstName from #TempPerson tp
join #TempDept td on tp.FakeDeptID = td.FakeDeptID
join dbo.Dept d on d.DeptName = td.DeptName
print 'Hey, I have a list of the new personids as well'
select * from #AuditTablePerson
print 'Update the #TempPerson with the NewIdentityPersonID captured by the
output'
Update #TempPerson Set PersonID = at.NewIdentityPersonID
From #TempPerson tp , #AuditTablePerson at
Where tp.SSN = at.SSN
print '------------<<'
print 'This was stuff left over from a previous example...good for learning,
but you dont need it'
select * from #TempPerson
print '------------<<<'
-----------------
print ''
print '------------------'
print 'Did it work?'
Select p.SSN, d.DeptName from dbo.Person p join dbo.Dept d on p.DeptID =
d.DeptID
select * from dbo.Dept
select * from dbo.Person
print 'Sure Did!!'
IF OBJECT_ID('tempdb..#TempPerson') IS NOT NULL
begin
drop table #TempPerson
end
IF OBJECT_ID('tempdb..#AuditTablePerson') IS NOT NULL
begin
drop table #AuditTablePerson
end
IF OBJECT_ID('tempdb..#TempDept') IS NOT NULL
begin
drop table #TempDept
end
IF OBJECT_ID('tempdb..#AuditTableDept') IS NOT NULL
begin
drop table #AuditTableDept
end
Miha Markic said:
Hi Scott,
This is usually done by adapter's insert SQL statement - there should be
appended a command (valid for SQL Server) INSERT ... ; SELECT
scope_identity() as PK_COLUMN;
The later inserts correct identity value into dataset after the record has
been inserted.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development
www.rthand.com
Blog: blog.rthand.com
Scott Ocamb said:
All,
I am using a typed dataset and populating it with a number of new rows of
data.
I then am updating the database like this
taEquipmentCRUD.Update(ds.EquipmentCRUD);
There will be multiple inserts done in the database. I need to get the
identity values from these inserts for a subsequent operation.
I would think this would be an easy thing to do, but i can't seem to
figure it out.
any help would be appreciated.
