Insert identity column value into table from another table?

Question

create table #test (a int identity(1,1), b varchar(20), c varchar(20))

insert into #test (b,c) values ('bvju','hjab')
insert into #test (b,c) values ('bst','sdfkg')
......
insert into #test (b,c) values ('hdsj','kfsd')

How would I insert the identity value (#test.a) that got populated from the above insert statements into #sample table (another table)

create table #sample (d int identity(1,1), e int, f varchar(20))

insert into #sample(e,f) values (identity value from #test table, 'jkhjk')
insert into #sample(e,f) values (identity value from #test table, 'hfhfd')
......
insert into #sample(e,f) values (identity value from #test table, 'khyy')

Could any one please explain how I could implement this for larger set of records (thousands of records)?

Can we use while loop and scope_identity? If so, please explain how can we do it?

what would be the scenario if i insert into #test from a select query?

insert into #test (b,c) select ... from ... (thousands of records)

How would i capture the identity value and use that value into another (#sample) insert into #sample(e,f) select (identity value from #test), ... from .... (thousand of records) –

Answer 1

You can use the output clause. From the documentation (emphasis mine):

The OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

like so:

create table #tempids (a int) -- a temp table for holding our identity values

insert into #test 
(b,c) 
output inserted.a into #tempids -- put the inserted identity value into #tempids
values 
('bvju','hjab')

You then asked...

What if the insert is from a select instead?

It works the same way...

insert into #test 
(b,c) 
output inserted.a into #tempids -- put the inserted identity value into #tempids
select -- except you use a select here
 Column1
,Column2
from SomeSource

It works the same way whether you insert from values, a derived table, an execute statement, a dml table source, or default values. If you insert 1000 records, you'll get 1000 ids in #tempids.

Answer 2

I just wrote up a "set based" sample with the output clause.

Here it is.

IF OBJECT_ID('tempdb..#DestinationPersonParentTable') IS NOT NULL
begin
        drop table #DestinationPersonParentTable
end



IF OBJECT_ID('tempdb..#DestinationEmailAddressPersonChildTable') IS NOT NULL
begin
        drop table #DestinationEmailAddressPersonChildTable
end



CREATE TABLE #DestinationPersonParentTable
(
PersonParentSurrogateIdentityKey int not null identity (1001, 1), 
SSNNaturalKey int, 
HireDate datetime
)



declare @PersonOutputResultsAuditTable table
(
SSNNaturalKey int, 
PersonParentSurrogateIdentityKeyAudit int
)





CREATE TABLE #DestinationEmailAddressPersonChildTable
(
DestinationChildSurrogateIdentityKey int not null identity (3001, 1), 
PersonParentSurrogateIdentityKeyFK int, 
EmailAddressValueNaturalKey varchar(64),
EmailAddressType int
)





-- Declare XML variable

DECLARE @data XML;

-- Element-centered XML

SET @data = N'
<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Person>
        <SSN>222222222</SSN>
        <HireDate>2002-02-02</HireDate>
    </Person>

    <Person>
        <SSN>333333333</SSN>
        <HireDate>2003-03-03</HireDate>
    </Person>

    <EmailAddress>
        <SSNLink>222222222</SSNLink>
        <EmailAddressValue>g@g.com</EmailAddressValue>
        <EmailAddressType>1</EmailAddressType>
    </EmailAddress>

    <EmailAddress>
        <SSNLink>222222222</SSNLink>
        <EmailAddressValue>h@h.com</EmailAddressValue>
        <EmailAddressType>2</EmailAddressType>
    </EmailAddress>

    <EmailAddress>
        <SSNLink>333333333</SSNLink>
        <EmailAddressValue>a@a.com</EmailAddressValue>
        <EmailAddressType>1</EmailAddressType>
    </EmailAddress>

    <EmailAddress>
        <SSNLink>333333333</SSNLink>
        <EmailAddressValue>b@b.com</EmailAddressValue>
        <EmailAddressType>2</EmailAddressType>
    </EmailAddress>

</root>

';




INSERT INTO #DestinationPersonParentTable ( SSNNaturalKey ,  HireDate )

output inserted.SSNNaturalKey , inserted.PersonParentSurrogateIdentityKey  into @PersonOutputResultsAuditTable ( SSNNaturalKey , PersonParentSurrogateIdentityKeyAudit)

SELECT T.parentEntity.value('(SSN)[1]', 'INT') AS SSN,
       T.parentEntity.value('(HireDate)[1]', 'datetime') AS HireDate
FROM @data.nodes('root/Person') AS T(parentEntity)
/* add a where not exists check on the natural key */
where not exists (
    select null from #DestinationPersonParentTable innerRealTable where innerRealTable.SSNNaturalKey = T.parentEntity.value('(SSN)[1]', 'INT') )
;

/* Optional.  You could do a UPDATE here based on matching the #DestinationPersonParentTableSSNNaturalKey = T.parentEntity.value('(SSN)[1]', 'INT')
You could Combine INSERT and UPDATE using the MERGE function on 2008 or later.
 */


select 'PersonOutputResultsAuditTable_Results' as Label, * from @PersonOutputResultsAuditTable


INSERT INTO #DestinationEmailAddressPersonChildTable (  PersonParentSurrogateIdentityKeyFK ,  EmailAddressValueNaturalKey , EmailAddressType )
SELECT  par.PersonParentSurrogateIdentityKeyAudit , 
        T.childEntity.value('(EmailAddressValue)[1]', 'varchar(64)') AS EmailAddressValue,
        T.childEntity.value('(EmailAddressType)[1]', 'INT') AS EmailAddressType
FROM @data.nodes('root/EmailAddress') AS T(childEntity)
/* The next join is the "trick".  Join on the natural key (SSN)....**BUT** insert the PersonParentSurrogateIdentityKey into the table */
join @PersonOutputResultsAuditTable par on par.SSNNaturalKey = T.childEntity.value('(SSNLink)[1]', 'INT')
where not exists (
    select null from #DestinationEmailAddressPersonChildTable innerRealTable where innerRealTable.PersonParentSurrogateIdentityKeyFK = par.PersonParentSurrogateIdentityKeyAudit AND  innerRealTable.EmailAddressValueNaturalKey = T.childEntity.value('(EmailAddressValue)[1]', 'varchar(64)'))
;



print '/#DestinationPersonParentTable/'
select * from #DestinationPersonParentTable


print '/#DestinationEmailAddressPersonChildTable/'
select * from #DestinationEmailAddressPersonChildTable


select SSNNaturalKey , HireDate , '---' as Sep1 , EmailAddressValueNaturalKey , EmailAddressType , '---' as Sep2, par.PersonParentSurrogateIdentityKey as ParentPK , child.PersonParentSurrogateIdentityKeyFK as childFK from #DestinationPersonParentTable par join #DestinationEmailAddressPersonChildTable child
on par.PersonParentSurrogateIdentityKey = child.PersonParentSurrogateIdentityKeyFK



IF OBJECT_ID('tempdb..#DestinationPersonParentTable') IS NOT NULL
begin
        drop table #DestinationPersonParentTable
end


IF OBJECT_ID('tempdb..#DestinationEmailAddressPersonChildTable') IS NOT NULL
begin
        drop table #DestinationEmailAddressPersonChildTable
end

Answer 3

insert into #test (b,c) values ('bvju','hjab')
insert into #sample(e,f) values (@SCOPE_IDENTITY(), 'jkhjk')

@SCOPE_IDENTITY() returns the last identity value used