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.