Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sql - Insert identity column value into table from another table?

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) –

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...