Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Merge command question

Author  Topic 

stratgeo
Starting Member

1 Post

Posted - 2013-01-30 : 17:00:15
Hi everyone. Quick question. Every week I get a CSV file with anywhere from 50 rows to 200,000 rows from a client. I use the import wizard and bring it into a DB and that works great. I would like to update this table every week with the data from the newest CSV and a Merge command appears to be the best route.

If the fields never change, just changes to existing records, records deleted or records added. Is Merge my only option?

There are about 300 fields so looking at this syntax it looks like a REALLY big block of code.

If I get a data update every week and the fields never change, is there a faster, more efficient way to do something similar to a Merge without having to type out all this information?

Thank you!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-01-30 : 17:26:58
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
Go to Top of Page
   

- Advertisement -