英文:
Query to Insert into Table1 Then update Table2 using inserted Identity
问题
可以帮助我创建一个查询，将数据插入到Table1，然后使用插入的标识更新Table2吗？
与标题相同，但我需要在SQL Server中执行此操作。我只需要执行一次，所以不想使用触发器。我只找到了如何在PostGres中执行此操作的方法，但我需要在TSQL中执行。我不了解PostGres SQL，但我根据找到的信息创建了下面的查询，它原本是用于PostGres的，但我需要在SQL Server中使用。
WITH ins AS (
INSERT INTO [dbo].[tbl1]
([Col1]
,[Col2])
SELECT
[Col1]
,[Col2]
FROM [dbo].[Tbl2]
WHERE ([Tbl2].[Tbl1Id] = 0 OR [Tbl2].[Tbl1Id] IS NULL)
GROUP BY [Col1], [Col2]
OUTPUT INSERTED.Tbl1Id, INSERTED.Col1, INSERTED.Col2
)
UPDATE [Tbl2]
SET [Tbl2].[Tbl1Id] = ins.[Tbl1Id]
WHERE [Tbl2].[Col1] = ins.[Col1]
AND [Tbl2].[Col2] = ins.[Col2]
请注意，我稍微修改了查询以在SQL Server中运行。
英文:
Can you help me create a Query to Insert into Table1 Then update Table2 using inserted Identity?
As with the title, but I need it in SQL Server. I need only to do it once so I want to do this without using triggers. I only found how to do it in PostGres, but I need it in TSQL. I don't know PostGres SQL but I used what I found to create the query below, which is supposed to be in PostGres, but I need it in SQL Server.
WITH ins AS (
INSERT INTO [dbo].[tbl1]
([Col1]
,[Col2])
SELECT
[Col1]
,[Col2]
FROM [dbo].[Tbl2]
where ([Tbl2].[Tbl1Id] = 0 OR [Tbl2].[Tbl1Id] is null)
GROUP BY [Col1], [Col2]
RETURNING Tbl1Id, Col1, Col2
)
UPDATE [Tbl2]
SET [Tbl2].[Tbl1Id] = ins.[Tbl1Id]
WHERE [Tbl2].[Col1] = ins.[Col1]
AND [Tbl2].[Col2]= ins.[Col2]
答案1
得分: 3
在插入或更新之后，没有returning关键字，而是可以使用OUTPUT关键字。另一个要注意的点是无法在CTE内部进行插入/更新。
你可以首先创建一个临时/变量表，然后在将其插入到tbl1时将语句的输出导出到临时/变量表中（https://stackoverflow.com/a/6292193/4818540），然后使用该临时/变量表来更新第二个表。
begin
DECLARE @InsertedIds TABLE (
Tbl1Id INT,
Col1 VARCHAR(50),
Col2 VARCHAR(50)
);
INSERT INTO dbo.tbl1 (Col1, Col2)
OUTPUT inserted.Tbl1Id, inserted.Col1, inserted.Col2 INTO @InsertedIds
SELECT Col1, Col2
FROM dbo.Tbl2
WHERE (Tbl2.Tbl1Id = 0 OR Tbl2.Tbl1Id IS NULL)
GROUP BY Col1, Col2;
UPDATE T2
SET T2.Tbl1Id = I.Tbl1Id
FROM dbo.Tbl2 T2
JOIN @InsertedIds I ON T2.Col1 = I.Col1 AND T2.Col2 = I.Col2;
end;
英文:
There's no returning keyword after the insertion or update, instead of it you can use the OUTPUT keyword, another point is you can't insert/update inside of CTE.
You can first create a temporal/variable table and then when inserting it into tbl1 export the output of the statement into the temporal/variable table(https://stackoverflow.com/a/6292193/4818540) and then use that temporal/variable table for updating your second table.
begin
DECLARE @InsertedIds TABLE (
Tbl1Id INT,
Col1 VARCHAR(50),
Col2 VARCHAR(50)
);
INSERT INTO dbo.tbl1 (Col1, Col2)
OUTPUT inserted.Tbl1Id, inserted.Col1, inserted.Col2 INTO @InsertedIds
SELECT Col1, Col2
FROM dbo.Tbl2
WHERE (Tbl2.Tbl1Id = 0 OR Tbl2.Tbl1Id IS NULL)
GROUP BY Col1, Col2;
UPDATE T2
SET T2.Tbl1Id = I.Tbl1Id
FROM dbo.Tbl2 T2
JOIN @InsertedIds I ON T2.Col1 = I.Col1 AND T2.Col2 = I.Col2;
end;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论