插入前如何获得下一个身份 [英] How to get next identity before insert

问题描述

嗨

<标记>我的文本框值之一应该是Mssql中的下一个标识.
该怎么做，请您帮忙?

问候
Lancy

Hi

<tag>One of my textbox value should be next identity from Mssql.
How to do that could you please help?

Regards
Lancy

推荐答案

IDENT_CURRENT(''tableName'')(包括单引号)返回给定表的标识的当前值.该值应该是表中最后使用的标识值.换句话说，除非表中已删除该行，否则您将在表中具有该标识值的行.将在下一个INSERT上分配的标识值将是IDENT_CURRENT(``tableName'')+ IDENT_INCR(``tableName'').

不过，我不建议您依靠它.如果以这种方式预先确定下一个标识值，则势必会遇到以下情况:另一个进程在插入之前先获得了该ID，而该进程实际上使用了错误的ID值. br/>
最好先插入(即使您还没有所有数据)，然后使用SCOPE_IDENTITY()获取分配的实际ID.

您可能想知道为什么SCOPE_IDENTITY()比IDENT_CURRENT(''tableName'')更好.顾名思义，前者将为您提供在当前范围内分配的最新身份值(您的批次，存储的过程，无论如何)，而后者将为您提供任何人在表上分配的最新身份.即使您可能在"INSERT"之后立即调用IDENT_CURRENT，也有可能在两者之间发生其他人的INSERT，并且IDENT_CURRENT将为您提供由其插入而不是您的插入产生的标识值，而SCOPE_IDENTITY将始终给你你的.

例如

IDENT_CURRENT(''tableName'') (include the single quotes) returns the current value of the identity for the given table. This value should be the last-assigned identity value used in the table. In other words, you will have a row with this identity value already in the table, unless that row has been deleted. The identity value that will be assigned on the next INSERT will be IDENT_CURRENT(''tableName'') + IDENT_INCR(''tableName'').

I don''t recommend relying on this, though. If you pre-determine the next identity value this way, you''re bound to end up in a situation where another process makes the insert that actually gets that ID before yours does, so your process ends up using the wrong ID value.

It''s much better to make your insert first (even if you don''t have all the data yet), and use SCOPE_IDENTITY() to get the actual ID assigned.

You might wonder why SCOPE_IDENTITY() is better than IDENT_CURRENT(''tableName''). As the name implies, the former will give you that most recent identity value assigned within your current scope (your batch, your stored proc, whatever), whereas the latter will give you the most recent identity assigned on the table, by anyone. Even though you might call IDENT_CURRENT right after ''INSERT, it''s still possible that someone else''s INSERT occurs in between, and IDENT_CURRENT will give you the identity value that resulted from their insert instead of yours, whereas SCOPE_IDENTITY will always give you yours.

eg

SELECT IDENT_CURRENT(''table_name'') as CurrentIdentity,
IDENT_INCR(''table_name'') as Increment,
IDENT_CURRENT(''table_name'') + IDENT_INCR(''table_name'') as NextIndentity

IDENT_Current，ScopeIdentity()，@@ Identity.它们都返回当前插入的Identity值.递增该值并将其分配给文本框的文本属性.

IDENT_Current,ScopeIdentity(),@@Identity . All of them return the current Identity value inserted. Increment the value and assign it to text proprty of text box.

这篇关于插入前如何获得下一个身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！