英文:
How create new record in multiple tables using newly create primary key in Child table
问题
我需要在表TableA中插入多行记录，其中主键是自动生成的Int，然后创建与TableA的主键相关的记录，建立1:1的关系。
TableA
IdA {主键}: int
名称: nvarchar
TableB
IdB {主键}: int
IdA {外键}: int
创建日期: DateTime
过期日期: DateTime
我希望在一个脚本下执行上述任务。
英文:
I need to Insert multiple rows in a table TableA where primary key is Int auto-generated, followed create records in TableB (1:1) relationship, using the primary key from TableA
TableA
IdA {PK}: int
name : nvchar
TableB
IdB {PK}: int
IdA {FK}: int
CreatedDate : DateTime
ExpriyDate: DateTime
I want to perform above task under one script
答案1
得分: 3
您可以使用 MERGE 与 OUTPUT 子句一起使用。MERGE 中的 OUTPUT 子句允许访问源表和目标表的列。
查询
MERGE 可以进行插入、更新和删除操作，但我们只需要简单的插入，因此联接条件始终为假（1=0）。
MERGE INTO TableA AS Dest
USING
(
SELECT name, CreatedDate, ExpiryDate
FROM #SomeSourceData
) AS Src
ON (1 = 0)
WHEN NOT MATCHED BY TARGET THEN
INSERT
(name)
VALUES
(Src.name)
OUTPUT inserted.IdA, Src.CreatedDate, Src.ExpiryDate
INTO TableB(IdA, CreatedDate, ExpiryDate);
在这里，我假设 TableA.IdA 是自增列，因此这一列没有在 INSERT 语句中显式列出。对于列 TableB.IdB 也是一样的。
对于 #SomeSourceData 中的每一行，将会插入一行到 TableA，并将一行相应的数据插入到 TableB 中。
英文:
You can use MERGE together with OUTPUT clause. OUTPUT clause in MERGE allows to access columns from both source and destination tables.
Query
MERGE can insert, update and delete, but we need only simple insert, so the join criteria is always false (1=0).
MERGE INTO TableA AS Dest
USING
(
SELECT name, CreatedDate, ExpiryDate
FROM #SomeSourceData
) AS Src
ON (1 = 0)
WHEN NOT MATCHED BY TARGET THEN
INSERT
(name)
VALUES
(Src.name)
OUTPUT inserted.IdA, Src.CreatedDate, Src.ExpiryDate
INTO TableB(IdA, CreatedDate, ExpiryDate)
;
Here I assume that TableA.IdA is IDENTITY, so this column is not explicitly listed in the INSERT statement. Same for column TableB.IdB.
For each row in #SomeSourceData there will be one row inserted into TableA and one corresponding row inserted into TableB.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论