sql-server - Insert OUTPUT Insert.id to another table in multiple values insert

Question

为了简单起见，假设我有两个表

用户表(id，email)

用户日志表(id, date)

无论 id 被插入到 user 表中，相同的 id 也应该被插入到 user_log 表中，否则事务应该失败。

我该怎么做

BEGIN TRANSACTION

INSERT into user(id, email) OUTPUT Inserted.id (1, 'a@x.com', 'x'), (2, 'b@x.com', 'y')  

// I also want to be able to do
INSERT into user_log(id, date) values(1, date), (2, date) 

COMMIT TRANSACTION

Answer 1

您可以将输出直接插入到 user_log 表中:

BEGIN TRANSACTION

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
VALUES (1, 'a@x.com'), (2, 'b@x.com');

COMMIT TRANSACTION  

Example on SQL Fiddle

---

如果您需要返回 ID，您可以添加第二个 OUTPUT 子句:

BEGIN TRANSACTION

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
OUTPUT inserted.id                      
VALUES (1, 'a@x.com'), (2, 'b@x.com');

COMMIT TRANSACTION