使用临时表比较 SQL Server 中存储过程中的 2 个表

Question

我正在尝试创建一个存储过程来比较 2 个表并使它们相同。我尝试了以下代码：

CREATE PROCEDURE SESUS.Compare2Tables
(@Table1 AS NVARCHAR(255),
@Table2 AS NVARCHAR(255),
@key AS NVARCHAR(MAX))
AS
BEGIN
IF OBJECT_ID ('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
--print 'SELECT * INTO #tmp FROM ' + @Table2  + ' except select * from ' + @Table1 + ';'
--print 'delete from ' + @table1 + ' where ' + @key + ' in (select ' + @key + ' from #tmp);'  
--print 'insert into ' +@table1 + ' select * from ' +@table2 + ' where ' +@key + ' in (select ' +@key + ' from #tmp);'
exec ('SELECT * INTO #tmp FROM ' + @Table2  + ' except select * from ' + @Table1 + ';')
exec ('delete from ' + @table1 + ' where ' + @key + ' in (select ' + @key + ' from #tmp)')
exec ('insert into ' +@table1 + ' select * from ' +@table2 + ' where ' +@key + ' in (select ' +@key + ' from #tmp)')
END

但是它返回以下错误，知道为什么它可以插入到这个临时表中，但无法从中选择吗？

对象名称"#tmp"无效。

Answer 1

可以使用 MERGE 语句与第二个表同步。

MERGE table1 AS target
USING table2 AS source
ON source.id = target.id
WHEN MATCHED THEN
UPDATE SET col1 = source.col1, col2 = ...
WHEN NOT MATCHED BY TARGET THEN
INSERT (id, col1, ...) VALUES (source.id, col1, ...)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;

Answer 2

尝试在一次执行中执行所有语句，因为临时表的范围有限：

exec ('SELECT * INTO #tmp FROM ' + @Table2  + ' except select * from ' + @Table1 + ';
delete from ' + @table1 + ' where ' + @key + ' in (select ' + @key + ' from #tmp)
insert into ' +@table1 + ' select * from ' +@table2 + ' where ' +@key + ' in (select ' +@key + ' from #tmp)')

您也可以使用全局温度(##tmp(表。

Answer 3

如果查询返回 null，则无法创建临时表。

create Procedure SESUS.Compare2Tables(
@Table1 as NVarchar(255),
@Table2 as NVarchar(255),
@key as NVarchar(max)
)
AS
BEGIN
IF OBJECT_ID ('tempdb..#tmp') is not null
DROP TABLE #tmp
--print 'SELECT * INTO #tmp FROM ' + @Table2  + ' except select * from ' + @Table1 + ';'
--print 'delete from ' + @table1 + ' where ' + @key + ' in (select ' + @key + ' from #tmp);'
--print 'insert into ' +@table1 + ' select * from ' +@table2 + ' where ' +@key + ' in (select ' +@key + ' from #tmp);'
exec ('SELECT * INTO #tmp FROM ' + @Table2  + ' except select * from ' + @Table1 + ';')
If(OBJECT_ID('tempdb..#tmp') Is Not Null)
Begin
exec ('delete from ' + @table1 + ' where ' + @key + ' in (select ' + @key + ' from #tmp)')
exec ('insert into ' +@table1 + ' select * from ' +@table2 + ' where ' +@key + ' in (select ' +@key + ' from #tmp)')
End
END