You can generate the tedious parts of the statement (all the column references) by selecting out meta data from sql - like Information_Schema.Columns. Once the code is working you can use it every time you get a new file.
EDIT:
so you would import into a staging table and MERGE into your actual table.
EDIT2:
Here ya go - I've used something like this before:
--generate <column list> with something like this:
select ',' + column_name
from information_schema.columns
where table_name = '<realTable>'
order by ordinal_position
--generate the <value list> with something like this:
select ',tmp.' + column_name
from information_schema.columns
where table_name = '<realTable>'
order by ordinal_position
--generate the <update condition> with something like this:
select 'and tmp.' + column_name + ' = rt.' + column_name
from information_schema.columns
where table_name = '<realTable>'
order by ordinal_position
--generate the <set statements> with something like this:
select ',rt.' + column_name + ' = tmp.' + column_name
from information_schema.columns
where table_name = '<realTable>'
order by ordinal_position
merge <realTable> as rt
using #staging as tmp
on tmp.PKCols = rt.PKCols
when not matched by target then
insert (<column list>)
values (<value list>)
when not matched by source then
delete
when matched and not (<update condition>)
then
update set
<set statements>
Be One with the Optimizer
TG
Merge command question
Posted - 01/30/2013 : 17:00:15
