blocks|key|1463535|text|insert+into+YourTable+values+(...)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1463536|使用scope_identity()获取新的PK|unstyled|1463537|select+scope_identity()|1463538|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|L|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|I|3|-4|5|F|7|N|8|@]|9|@]|A|$]]]|J|$]]

<pre><code>insert into YourTable values (...)
</code></pre>

get the new PK with scope_identity()

<pre><code>select scope_identity()
</code></pre>

blocks|key|1463711|text|我的天！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463712|只需调用SCOPE_IDENTITY()函数：|offset|length|style|CODE|1463713|insert+into+your_talble(col1,col2)+values('blah','more+blah')
select+scope_identity()|code-block|syntax|javascript|1463714|因为如果选择最高值，则在任何其他语句执行insert时都会返回错误。函数scope_identity()返回在当前上下文中(即通过您的语句)创建的标识。|1463715|entityMap^0|0|4|G|0|0|10|G|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

holy crap!!!

just call <code>SCOPE_IDENTITY()</code> function:

<pre><code>insert into your_talble(col1,col2) values('blah','more blah')
select scope_identity()
</code></pre>

because selecting highest value will return error if any other statement make an insert. the function <code>scope_identity()</code> returns the identity created in current context (that is by your statement)

blocks|key|1463763|text|SCOPE_IDENTITY()可能就是您想要的。它返回由执行它的相同代码上下文插入的最后一条记录的ID。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463764|IDENT_CURRENT('tablename')存在并发问题。也就是说，不能保证在插入和调用IDENT_CURRENT之间不会插入另一条记录。|1463765|我必须承认，我不确定这个小笨蛋的爆发指的是什么令人惊讶的来源，但我自己也很惊讶这个问题似乎根本不是重复的。|1463766|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

SCOPE_IDENTITY() is probably what you want. It returns the ID of the last record inserted by the same code context in which it executes.

IDENT_CURRENT('tablename') is subject to concurrency issues. That is, there's no guarantee that another record won't be inserted between the INSERT and the call to IDENT_CURRENT.

I must confess, I'm not sure to what source of amazement the VillageIdiot's outburst refers, but I myself am quite astonished that this question does not appear to be a duplicate at all.

blocks|key|1249058|text|如果使用的是SQL+Server2005或更高版本，则可以使用OUTPUT子句。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1249059|create+table+T(
++pk+int+identity+primary+key,
++dat+varchar(20)
);
go

insert+into+T
output+inserted.pk
values+('new+item');
go

drop+table+T;|code-block|syntax|javascript|1249060|输出既可以定向到表，也可以定向到客户端。例如：|1249061|create+table+T(
++pk+int+identity+primary+key,
++dat+varchar(20)
);

create+table+U(
++i+int+identity(1001,1)+primary+key,
++T_pk+int+not+null,
++d+datetime
);
go


insert+into+T
output+inserted.pk,+getdate()
into+U(T_pk,d)
values+('new+item'),+('newer+item');
go

select+*+from+T;
select+*+from+U;
go

drop+table+T,+U;|1249062|从SQL+Server2008开始，您可以使用"composable+DML“来实现更多可能性。|1249063|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you are using SQL Server 2005 or later, you can use the OUTPUT clause. 

<pre><code>create table T(
 pk int identity primary key,
 dat varchar(20)
);
go

insert into T
output inserted.pk
values ('new item');
go

drop table T;
</code></pre>

The output can be directed to a table as well as to the client. For example:

<pre><code>create table T(
 pk int identity primary key,
 dat varchar(20)
);

create table U(
 i int identity(1001,1) primary key,
 T_pk int not null,
 d datetime
);
go


insert into T
output inserted.pk, getdate()
into U(T_pk,d)
values ('new item'), ('newer item');
go

select * from T;
select * from U;
go

drop table T, U;
</code></pre>

Beginning with SQL Server 2008, you can use "composable DML" for more possibilities.

blocks|key|1463850|text|您应该使用scope_identity()。我建议将insert语句和scope_identity()包装到事务中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1463851|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You should use scope_identity(). And I recommend to wrap insert statement and scope_identity() into transaction.

blocks|key|1249155|text|INSERT+INTO+YourTable+(1,+2,+etc.)
OUTPUT+inserted.yourIDcolumn
VALUES+(value1,+value2,+value...)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1249156|注意:这是针对MS+SQL+2005及更高版本的|unstyled|1249157|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>INSERT INTO YourTable (1, 2, etc.)
OUTPUT inserted.yourIDcolumn
VALUES (value1, value2, value...)
</code></pre>

Note: This is for MS SQL 2005 and greater

I have a primary key set up to auto increment.

I am doing multiple queries and I need to retrieve that primary key value to use as a foreign key in another table (<code>IsIdentity = TRUE</code>).

Is there any elegant way to get back the primary key value when I do an insert query? Right now I am requerying and getting the highest value in that column which seems really hacky.

Any suggestions?

How do I insert into a table and get back the primary key value?

SQL Server

我将主键设置为自动递增。我正在执行多个查询，并且需要检索该主键值以用作另一个表(IsIdentity = TRUE)中的外键。在执行insert查询时，有没有什么很好的方法来取回主键值？现在，我正在重新查询，并在该列中获得最高值，这似乎真的很麻烦。有什么建议吗？

问如何插入到表中并取回主键值？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何插入到表中并取回主键值？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何插入到表中并取回主键值？
EN