Solved

UPSERT in SSIS

Posted on 2015-02-06
4
166 Views
Last Modified: 2016-02-15
I have an ERP system with an ORD_HDR(about a million rows) table and an ORD_DTL(about 7 million rows) table. Initially there was an ODBC connection to the ERP system and it was a query  on the ORD_HDR table with an INNER JOIN to the details table for the last 10 days with a Slowly Changing dimension to insert or update the table and then a bunch of Lookups and then loading a Fact table. This took over an hour and a half to run. So I loaded all the data into SQL tables and then want to use those tables to do the UPSERT. My initial problem is that I have to check the ERP data against my tables that are loaded already and do an Insert or Update if it exists already or not. I wanna do this the fastest way possible so Im open to suggestions. I have to query the ERP tables, match the records against the tables I loaded today and do an Insert if it doesnt exist and an Update if it does. I dont even beleive the Update is necessary but I guess it was necessary on the SCD I guess? Please help!!!
0
Comment
Question by:jknj72
  • 3
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40595178
>Insert if it doesnt exist and an Update if it does.
Have you tried a MERGE statement, which can perform inserts, updates, and deletes in a single statement?  

Scoll down for examples.  Assumes 2008 and higher.
0
 

Author Comment

by:jknj72
ID: 40596284
This may be exactly what Im looking for I just have to figure out how to write the syntax, I have like 200+ fields in my select statement. Can I just check like 3 source fields against 3 dest fields and then do the insert with all the columns? If this works I will buy you dinner!!!!
0
 

Author Comment

by:jknj72
ID: 40596861
Jim, do you have any experience with the Merge task in SSIS?
0
 

Author Closing Comment

by:jknj72
ID: 40596927
Thanks for pointing me in the right direction. I have another question out there for how to do the Merge, if your interested
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now