blocks|key|2386940|text|最好的方法是使用TSQL中的SCOPE_IDENTITY()函数。这应该作为插入的一部分来执行，即|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2386941|SqlCommand+cmd+=+new+SqlCommand(@"
++++INSERT+INTO+T+(Name)+VALUES(@Name)
++++SELECT+SCOPE_IDENTITY()+As+TheId",+conn);
cmd.AddParameter("@Name",+SqlDbType.VarChar,+50).Value+=+"Test";
int+tId+=+(int)cmd.ExecuteScalar();|code-block|syntax|javascript|2386942|或者，您可以将SCOPE_IDENTITY()赋值给要在后续语句中使用的变量。例如：|2386943|DECLARE+@T1+int

INSERT+INTO+T+(Name)+VALUES('Test')

SELECT+@T1+=+SCOPE_IDENTITY()+

INSERT+INTO+T2+(Name,+TId)+VALUES('Test',+@T1)|2386944|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

The best way of doing this is the use SCOPE_IDENTITY() function in TSQL. This should be executed as part of the insert i.e.

<pre><code>SqlCommand cmd = new SqlCommand(@"
 INSERT INTO T (Name) VALUES(@Name)
 SELECT SCOPE_IDENTITY() As TheId", conn);
cmd.AddParameter("@Name", SqlDbType.VarChar, 50).Value = "Test";
int tId = (int)cmd.ExecuteScalar();
</code></pre>

Alternatively you can assign SCOPE_IDENTITY() to a variable to be used in successive statements. e.g.

<pre><code>DECLARE @T1 int

INSERT INTO T (Name) VALUES('Test')

SELECT @T1 = SCOPE_IDENTITY() 

INSERT INTO T2 (Name, TId) VALUES('Test', @T1)
</code></pre>

blocks|key|1321184|text|获取插入行的标识的方法是使用SCOPE_IDENTITY()函数。如果您使用的是存储过程，则返回行标识作为输出参数的代码如下所示。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1321185|CREATE+PROCEDURE+dbo.MyProcedure
(
++++@RowId+INT+=+NULL+OUTPUT
)
AS

INSERT+INTO+MyTable
(
++++Column1
++++,Column2
++++,...
)
VALUES
(
++++@Param1
++++,@Param2
++++,...
);

SET+@RowId+=+SCOPE_IDENTITY();|code-block|syntax|javascript|1321186|然后，您可以在任何后续插入中使用此值(或者，如果您可以将所有数据都传递到存储过程中，则可以在过程主体的其余部分中使用它)。|1321187|如果你动态地传递SQL，那么你可以使用几乎相同的技术，但是使用一个带有语句分隔符的字符串(在SQL中也是;+)，例如：|1321188|var+sql+=+"INSERT+INTO+MyTable+(Column1,+Column2,+...)+VALUES+(@P1,+@P2,+...);"+%2B
++++++++++"SELECT+SCOPE_IDENTITY();";|1321189|然后，如果您使用ExecuteScalar执行此操作，您将能够将标识作为标量结果返回并将其转换为正确的类型。或者，您可以一次性构建整个批次，例如|1321190|var+sql+=+"DECLARE+@RowId+INT;"+%2B+
++++++++++"INSERT+INTO+MyTable+(Column1,+Column2,+...)+VALUES+(@P1,+@P2,+...);"+%2B
++++++++++"SET+@RowId+=+SCOPE_IDENTITY();"+%2B
++++++++++"INSERT+INTO+MyOtherTable+(Column1,+...)+VALUES+(@P3,+@P4,+...);";|1321191|这可能不是完全正确的语法，并且您可能需要在一开始就使用SET+NOCOUNT+ON;+(我的想法有点生疏，因为我很少使用dynamic+SQL)，但它应该会让您走上正确的道路。|1321192|entityMap^0|E|G|0|0|0|1G|1|0|0|8|D|0|0|R|F|0^^$0|@$1|2|3|4|5|6|7|Y|8|@$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@]|E|$]]|$1|O|3|P|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|S|3|T|5|H|7|1A|8|@]|D|@]|E|$I|J]]|$1|U|3|V|5|6|7|1B|8|@$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|W|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|X|$]]

The way to get the identity of the inserted row is with the <code>SCOPE_IDENTITY()</code> function. If you're using stored procedures then this would look something like the following to return the row identity as an output parameter.

<pre><code>CREATE PROCEDURE dbo.MyProcedure
(
 @RowId INT = NULL OUTPUT
)
AS

INSERT INTO MyTable
(
 Column1
 ,Column2
 ,...
)
VALUES
(
 @Param1
 ,@Param2
 ,...
);

SET @RowId = SCOPE_IDENTITY();
</code></pre>

You can then use this value for any subsequent inserts (alternatively, if you can pass the data all into the stored procedure, then you can use it in the remainder of the procedure body).

If you're passing the SQL in dynamically then you use much the same technique, but with a single string with statement delimiters (also <code>;</code> in SQL), e.g.:

<pre><code>var sql = "INSERT INTO MyTable (Column1, Column2, ...) VALUES (@P1, @P2, ...);" +
 "SELECT SCOPE_IDENTITY();";
</code></pre>

Then if you execute this using <code>ExecuteScalar</code> you'll be able to get the identity back as the scalar result and cast it to the right type. Alternatively you could build up the whole batch in one go, e.g.

<pre><code>var sql = "DECLARE @RowId INT;" + 
 "INSERT INTO MyTable (Column1, Column2, ...) VALUES (@P1, @P2, ...);" +
 "SET @RowId = SCOPE_IDENTITY();" +
 "INSERT INTO MyOtherTable (Column1, ...) VALUES (@P3, @P4, ...);";
</code></pre>

This may not be exactly the right syntax, and you may need to use <code>SET NOCOUNT ON;</code> at the start (my mind is rusty as I rarely use dynamic SQL) but it should get you on the right track.

blocks|key|2386973|text|如果您只是使用SQL，那么请检查Duncan的答案。但是，如果您使用的是LINQ，那么您可以创建实体，将其保存到数据库中，ID参数将自动填充。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2386974|给定一个用户实体和一个用户表，它可能如下所示：|2386975|using(var+db+=+new+DataContext())+{
++++var+user+=+new+User+{+Name+=+"Jhon"+};
++++db.Users.InsertOnSubmit(user);
++++db.SubmitChanges();
++++/*+At+this+point+the+user.ID+field+will+have+the+primary+key+from+the+database+*/
}|code-block|syntax|javascript|2386976|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you are just using SQL then check Duncan's answer. If however you are using LINQ then you can create the entity, save it to the DB and the ID parameter will be populated automatically.

Given a user entity and a user table it might look like this:

<pre><code>using(var db = new DataContext()) {
 var user = new User { Name = "Jhon" };
 db.Users.InsertOnSubmit(user);
 db.SubmitChanges();
 /* At this point the user.ID field will have the primary key from the database */
}
</code></pre>

I am currently working in C#, and I need to insert a new record into one table, get the new primary key value, and then use that as a foreign key reference in inserting several more records. The Database is MS SQL Server 2003. All help is appreciated!

Getting autonumber primary key from MS SQL Server

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我目前正在使用C#，我需要将一条新记录插入到一个表中，获得新的主键值，然后在插入多条记录时将其用作外键引用。数据库为MS SQL Server 2003。感谢所有的帮助！

问从MS SQL Server获取自动编号主键
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从MS SQL Server获取自动编号主键EN