T-SQL 脚本帮助:插入记录，然后在另一条语句上使用该插入的标识? [英] Help with T-SQL script: Insert record, then use identity of that insert on another statement?

问题描述

作为序言，我对 T-SQL 语法不是很了解.

Just as a preface, I'm not very knowledgeable on T-SQL syntax.

我想创建一个简单的 SQL 脚本，该脚本将生成 3 个插入语句.

I'd like to create a simple SQL script that will make 3 insert statements.

Insert A
Insert B
Insert C

在插入 B 语句中需要插入 A 语句的标识或ID".并且两个身份都插入了 A &在 Insert C 语句中需要 B.

Insert A statement's identity or "ID" would be needed in insert B statement. And both the identities Inserts A & B would be needed in Insert C statement.

伪代码类似于:

INSERT INTO tableA
VALUES ('blah', 'blah')

INSERT INTO tableB
VALUES (IDENTITY_FROM_A_INSERT, 'foo')

INSERT INTO tableC
VALUES (IDENTITY_FROM_A_INSERT, IDENTITY_FROM_B_INSERT)

我将如何编写此脚本?

推荐答案

使用 SCOPE_IDENTITY() 在每次插入后获取插入行的标识(在当前会话中).

Use SCOPE_IDENTITY() after each insert in order to get the identity of the inserted row (in the current session).

我使用了两个变量来捕获两个身份，然后将它们插入到第三个表中:

I have used two variables to capture the two identities and then insert them into the third table:

DECLARE @Id1 INT
DECLARE @Id2 INT

INSERT INTO tableA VALUES ('blah', 'blah')

SET @Id1 = SELECT SCOPE_IDENTITY()

INSERT INTO tableB VALUES (IDENTITY_FROM_A_INSERT, 'foo')

SET @Id2 = SELECT SCOPE_IDENTITY()

INSERT INTO tableC VALUES (@Id1, @Id2)

这篇关于T-SQL 脚本帮助:插入记录，然后在另一条语句上使用该插入的标识?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！