MERGE Command in SQL Server

Question

Hi folks,

I have been using the statement

insert into target 
select * from source 
where [set of conditions] for a while.

Recently found this MERGE command that will be more effective to use for my purpose so that I can change the above statement to

MERGE target 
USING source ON [my condtion] 
WHEN NOT MATCHED BY TARGET 
THEN INSERT VALUES (source.col1, source.col2, source.col3) 

But the problem for me is lets say if I have 20+ columns in my source table I have to list all of them, I need a way to specify it to insert source.* . Is there a way ? I'm new to SQL. Appreciate your help.

Thanks in advance :)

Answer 1

Everything that I have read regarding the MERGE statement says that you need to specify the columns for your INSERT statement. If you are looking for a quick way to get the INSERT statment, you can right mouse click the table in SSMS and select Script Table As->INSERT To->Clipboard. You can then paste this into your query and alter just the VALUES part.

Merge statement

Answer 2

Me too; I hate typing column names.

I normally build the Merge statement in dynamic SQL.
I have a function that takes a table name as a parameter, and returns a string containing all column names formatted properly with Table Name prefix, [] brackets and comma, as in S.Col1, S.Col2, S.Col3

I could also tell you that I build a temp table with the required columns, and pass the temp table to my function, because some times you don't want a list of all columns. But that would probably be a confusing wooble, obscuring the important bits;

1. Use dynamic sql
2. Use a function to create csv list of columns.

Answer 3

There's simply no advantage of using MERGE in this situation. Why overcomplicate? Stick to the KISS principle, for chrissake.

Anyways, here's the script:

declare 
    @targetTableName varchar(100) = 'target'
    ,@targetSchemaName varchar(20) = 'dbo'
    ,@sourceTableName varchar(100) = 'source'
    ,@sourceSchemaName varchar(20) = 'dbo2'
    ,@matchCondition varchar(50) = 't.id = s.id'
    ,@columns varchar(max)

set @columns = (select ','+quotename(c.name)
from sys.tables t 
join sys.columns as c on t.object_id = c.object_id
join sys.schemas s on s.schema_id = t.schema_id
where t.name = @targetTableName and s.name = isnull(@targetSchemaName, s.name)
for xml path(''))

--a column name starts with a comma

declare @sql varchar(max) =  '
merge @target t 
using @source s on @matchCondition
when not matched then 
insert (@columns)
values @sourceColumns'

set @sql = 
replace(replace(replace(replace(replace(@sql
, '@matchCondition', @matchCondition)
 --replace @columns with column list with the first comma removed
, '@columns', stuff(@columns, 1, 1, ''))
--replace @sourceColumns with column list with the 's.' prefix and comma removed
, '@sourceColumns', stuff(replace(@columns, ',', ',s.'),1,1,''))
, '@target', quotename(@targetSchemaName)+'.'+quotename(@targetTableName))
, '@source', quotename(@sourceSchemaName)+'.'+quotename(@sourceTableName))

print @sql
--exec(@sql)

And we'll get something like this:

merge [dbo].[target] t 
using [dbo2].[source] s on t.id = s.id
when not matched then 
insert ([column1], [column2], [column3], [column4])
values s.[column1], s.[column2], s.[column3], s.[column4]