Solved

UPSERT in SSIS

Posted on 2015-02-06
4
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Related to SQL Query 5 21
SQL Server Compression Decision 5 49
Make an array show the subkey and put it in a query 2 29
sql server major issue  need help 2 51
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Viewers will learn how the fundamental information of how to create a table.

730 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