Using Variables to contain column names for SQL Server Merge statemtns

Question

I'm trying to automate the column list grab for a merge job, and running into a bit of a head scratcher. I don't want to have to manually type out all of the column names, and thought I may be able to pass them through via a variable. When selecting the content of my variables, its lists correctly. I know the update component is not written correctly (I'll gladly take feedback on how to streamline that), but what I'm concerned about is the "Insert Values(@sourcecolumns)" which is throwing an error: "Column name or number of supplied values does not match table definition."

What I'm passing through is below:

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

EDIT: @Max Vernon That is correct regarding updating the set piece. Once this is created, its created, but to avoid having to type this out for 205 columns I'd like to automate it. Additionally this will be reapplied to other tables, so I'd like to know the best/most efficient way to do it. How to automate the parameters so that:

Update set @targcolumns.A = @sourcecolumns.A, @[email protected], etc.

There are 205 columns in this particular table, and unfortunately table structure is not something we have control over. All in all there will be about 150 tables being merged routinely.

Edit2: Final Edit - I just wanted to give the solution I'm using for anyone who comes across this in the future. This contains the dynamic SQL that Max recommended as well as my query to set up the SET statements

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;

Note that I don't use all of the variables in this example, but do have other examples where they are all necessary. Hope this helps and thanks Max for setting me on the right path.

Answer 1

Instead of typing the entire list of column names, you can simply drag-and-drop the column list from the Object Explorer window in SQL Server Management Studio. In the image below, you drag-and-drop the highlighted section over to the query window:

However, if you insist on automatically creating the list of columns, you'll need to run the MERGE command using dynamic SQL. Erland Sommarskog has the definitive article on how to use dynamic SQL, along with a great set of advice on how not to do it, at http://www.sommarskog.se/dynamic_sql.html

Something like this:

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;

The above statement is not syntactically correct, but essentially shows how you need to concatenate parts of the query that are dynamic. You'll need to compile the @TargetToSourceColumns and @SourceColumns variables as needed.

Answer 2

I just wanted to give the solution I'm using for anyone who comes across this in the future. This contains the dynamic SQL that Max recommended as well as my query to set up the SET statements:

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;

Note that I don't use all of the variables in this example, but do have other examples where they are all necessary. Hope this helps and thanks Max for setting me on the right path.