SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Merge command question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stratgeo
Starting Member

USA
1 Posts

Posted - 01/30/2013 :  17:00:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/30/2013 :  17:26:58  Show Profile  Reply with Quote
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

Edited by - TG on 01/30/2013 17:39:31
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000