将标识列值从另一个表插入到表中? [英] 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')

如何将从上述插入语句填充的标识值 (#test.a) 插入#sample 表(另一个表)

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

我们可以使用while 循环和scope_identity 吗?如果是这样，请解释我们该怎么做?

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

如果我从选择查询中插入 #test 会出现什么情况?

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

插入#test(b,c)select ... from ...(数千条记录)

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

我如何捕获身份值并将该值用于另一个(#sample)插入#sample(e,f)select (identity value from #test), ... from .... (千条记录) –

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

推荐答案

您可以使用 output 子句.来自文档(强调我的):

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

OUTPUT 子句从受影响的每一行返回信息或基于表达式的信息通过 INSERT、UPDATE、DELETE 或 MERGE 语句.这些结果可以返回到处理应用程序以用于诸如确认消息、存档和其他此类应用程序要求.结果也可以插入表格或表格 此外，您可以捕获 OUTPUT 的结果嵌套的 INSERT、UPDATE、DELETE 或 MERGE 语句中的子句，以及将这些结果插入到目标表或视图中.

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.

像这样:

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

然后你问...

如果插入来自选择呢?

它的工作方式相同...

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

无论是从值、派生表、执行语句、dml 表源还是默认值插入，它的工作方式都相同.如果您插入 1000 条记录，您将在 #tempids 中获得 1000 个 ID.

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.

这篇关于将标识列值从另一个表插入到表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！