使用變數包含 SQL Server 合併狀態的列名

我正在嘗試自動執行合併作業的列列表抓取，並且遇到了一些麻煩。我不想手動輸入所有列名，並認為我可以通過變數傳遞它們。選擇我的變數的內容時，它的列表正確。我知道更新組件沒有正確編寫（我很樂意就如何簡化它提出回饋），但我擔心的是“插入值（@sourcecolumns）”，它會引發錯誤：“列名或提供的值的數量與表定義不匹配。”

我正在通過的內容如下：

CREATE PROCEDURE sys_sp_merge_table_name

@targcolumns varchar(max),
@targtable varchar(50),
@sourcecolumns varchar(max)

AS
BEGIN
SET NOCOUNT ON;
set @targcolumns =  '' --column names for the table
set @targtable = 'targ_table_name' -- target table name
set @sourcecolumns = ''

--assigning column names to pass to variables using "Tsource" as alias for table name 
select @targcolumns = @targcolumns + column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @sourcecolumns = @sourcecolumns + 'Tsource.'+column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable

--trim the trailing commas from target and source table
set @sourcecolumns = Left(@sourcecolumns,len(@sourcecolumns)-1)
set @targcolumns = Left(@targcolumns,len(@targcolumns)-1)

--run merge job, zdata is our linked server from which the data is imported
merge targ_table_name
using (select * from openquery(zdata,'select * from source_table_name'))as Tsource 
on (targ_table_name.ID = Tsource.ID) 
when matched then
Update set @targcolumns = @sourcecolumns
when not matched by target then
insert values (@sourcecolumns);
END
GO

編輯：@Max Vernon 關於更新定位球是正確的。一旦它被創建，它就被創建了，但是為了避免為 205 列輸入這個，我想自動化它。此外，這將被重新應用於其他表格，所以我想知道最好/最有效的方法。如何自動化參數，以便：

Update set @targcolumns.A = @sourcecolumns.A, @targcolumns.B=@sourcecolumns.B, etc.

這個特定的表中有 205 列，不幸的是，表結構不是我們可以控制的。總而言之，將有大約 150 個表被例行合併。

Edit2：最終編輯 - 我只是想為將來遇到此問題的任何人提供我正在使用的解決方案。這包含 Max 推薦的動態 SQL 以及我設置 SET 語句的查詢

Declare
@targcolumns varchar(max),
@targtable varchar(50),
@sourcecolumns varchar(max),
@runmerge nvarchar(max),
@setstatement nvarchar(max)


SET NOCOUNT ON;
set @targcolumns =  '' --column names for the table
set @targtable = 'target_table_name' -- target table name
set @sourcecolumns = ''

--assigning column names to pass to variables using "Tsource" as alias for table name 
select @targcolumns = @targcolumns + column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @sourcecolumns = @sourcecolumns + 'Tsource.'+column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @setstatement = @setstatement + '[' + column_name + ']' + ' = ' + 'Tsource.'+'[' + column_name+']'+','from INFORMATION_SCHEMA.columns where table_name = @targtable

--trim the trailing commas from target and source table
set @sourcecolumns = Left(@sourcecolumns,len(@sourcecolumns)-1)
set @targcolumns = Left(@targcolumns,len(@targcolumns)-1)
set @setstatement = Left(@setstatement,len(@setstatement)-1)

--run merge job, zdata is our linked server from which the data is imported
set @runmerge=' merge target_table_name
using (select * from openquery(zdata,''select * from target_table_name''))as Tsource 
on (target_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @setstatement + '
when not matched by target then
insert values ('+@sourcecolumns+');';

exec sp_executesql @runmerge;

請注意，我沒有使用此範例中的所有變數，但確實有其他範例，它們都是必需的。希望這會有所幫助，並感謝 Max 讓我走上了正確的道路。

我只是想為將來遇到此問題的任何人提供我正在使用的解決方案。這包含 Max 推薦的動態 SQL 以及我設置SET語句的查詢：

Declare
@targcolumns varchar(max),
@targtable varchar(50),
@sourcecolumns varchar(max),
@runmerge nvarchar(max),
@setstatement nvarchar(max)


SET NOCOUNT ON;
set @targcolumns =  '' --column names for the table
set @targtable = 'target_table_name' -- target table name
set @sourcecolumns = ''

--assigning column names to pass to variables using "Tsource" as alias for table name 
select @targcolumns = @targcolumns + column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @sourcecolumns = @sourcecolumns + 'Tsource.'+column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @setstatement = @setstatement + '[' + column_name + ']' + ' = ' + 'Tsource.'+'[' + column_name+']'+','from INFORMATION_SCHEMA.columns where table_name = @targtable

--trim the trailing commas from target and source table
set @sourcecolumns = Left(@sourcecolumns,len(@sourcecolumns)-1)
set @targcolumns = Left(@targcolumns,len(@targcolumns)-1)
set @setstatement = Left(@setstatement,len(@setstatement)-1)

--run merge job, zdata is our linked server from which the data is imported
set @runmerge=' merge target_table_name
using (select * from openquery(zdata,''select * from target_table_name''))as Tsource 
on (target_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @setstatement + '
when not matched by target then
insert values ('+@sourcecolumns+');';

exec sp_executesql @runmerge;

請注意，我沒有使用此範例中的所有變數，但確實有其他範例，它們都是必需的。希望這會有所幫助，並感謝 Max 讓我走上了正確的道路。

您可以簡單地從 SQL Server Management Studio 的“對象資源管理器”視窗拖放列列表，而不是鍵入整個列名列表。在下圖中，您將突出顯示的部分拖放到查詢視窗：

但是，如果您堅持自動創建列列表，則需要MERGE使用動態 SQL 執行命令。Erland Sommarskog 在http://www.sommarskog.se/dynamic_sql.html上有關於如何使用動態 SQL 的權威文章，以及關於如何不這樣做的一組很好的建議

像這樣的東西：

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'merge targ_table_name
using (select * from openquery(zdata,''select * from source_table_name'')) 
    as Tsource 
on (targ_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @TargetToSourceColumns + '
when not matched by target then
insert values (' + @SourceColumns + ');';

EXEC sp_executesql @cmd;

上面的語句在語法上不正確，但基本上顯示了您需要如何連接動態的查詢部分。您需要根據需要編譯@TargetToSourceColumns和@SourceColumns變數。

引用自：https://dba.stackexchange.com/questions/137243