2

I have database table of 100,000 rows, imported from CSV each week using an SSIS package. Usually updates, but sometimes it can add rows.

I see a few exceptions with staging table during the update rows - I don't know why? and how to update from staging to destination table?

This is the merge code :

MERGE INTO [PWCGFA_BG].[dbo].[Bank_Guarantees] WITH (HOLDLOCK) AS bg USING [PWCGFA_BG].[dbo].[stagingBG] AS stgbg ON bg.IATA_CODE = stgbg.IATA_CODE WHEN MATCHED THEN UPDATE set bg.LEGAL_NAME=stgbg.LEGAL_NAME, bg.TRADING_NAME=stgbg.TRADING_NAME, bg.COUNTRY=stgbg.COUNTRY, bg.CURRENCY=stgbg.CURRENCY, bg.LANGUAGE=stgbg.LANGUAGE, bg.STATUS=stgbg.STATUS, bg.BANK_NAME=stgbg.BANK_NAME, bg.BANK_GUARANTEE_AMOUNT=stgbg.BANK_GUARANTEE_AMOUNT, bg.BANK_GUARANTEE_CURRENCY=stgbg.BANK_GUARANTEE_CURRENCY, bg.BANK_GUARANTEE_EXPIRY_DATE=stgbg.BANK_GUARANTEE_EXPIRY_DATE, bg.ACCREDITATION_DATE=stgbg.ACCREDITATION_DATE, bg.CLASS_PAX_OR_CGO=stgbg.CLASS_PAX_OR_CGO, bg.LOCATION_TYPE=stgbg.LOCATION_TYPE, bg.XREF=stgbg.XREF, bg.IRRS=stgbg.IRRS, bg.TAX_CODE=stgbg.TAX_CODE, bg.COUNTRY_CODE=stgbg.COUNTRY_CODE, bg.CITY=stgbg.CITY, bg.DEF=stgbg.DEF, bg.OWN_SHARE_CHANGE=stgbg.OWN_SHARE_CHANGE WHEN NOT MATCHED BY bg THEN INSERT (IATA_CODE,LEGAL_NAME,TRADING_NAME,COUNTRY,CURRENCY,LANGUAGE,STATUS,BANK_NAME,BANK_GUARANTEE_AMOUNT,BANK_GUARANTEE_CURRENCY,BANK_GUARANTEE_EXPIRY_DATE,ACCREDITATION_DATE,CLASS_PAX_OR_CGO,LOCATION_TYPE,XREF,IRRS,TAX_CODE,CITY,DEF,OWN_SHARE_CHANGE) VALUES (stgbg.IATA_CODE,stgbg.LEGAL_NAME,stgbg.TRADING_NAME,stgbg.COUNTRY,stgbg.CURRENCY,stgbg.LANGUAGE,stgbg.STATUS,stgbg.BANK_NAME,stgbg.BANK_GUARANTEE_AMOUNT,stgbg.BANK_GUARANTEE_CURRENCY,stgbg.BANK_GUARANTEE_EXPIRY_DATE,stgbg.ACCREDITATION_DATE,stgbg.CLASS_PAX_OR_CGO,stgbg.LOCATION_TYPE,stgbg.XREF,stgbg.IRRS,stgbg.TAX_CODE,stgbg.CITY,stgbg.DEF,stgbg.OWN_SHARE_CHANGE) WHEN NOT MATCHED BY stgbg THEN DELETE

enter image description here

Improve this question
7
  • What is "a few exp" ? Exceptions? You need to tell us what the exceptions are together with some source data. Commented Jul 8, 2016 at 8:09
  • Few examples, I mean that for the update, they use staging table but I don't know how they insert from staging to destination DB, in my case I made a stored procedure in ssms for the update, and execute it in OLE DB command Commented Jul 8, 2016 at 8:12
  • Maybe you should transform your sp to do a MERGE from the staging table to Bank_Guarantees instead of only an UPDATE. In that case you could get rid of the Lookup and the Bank_Guarantees Destination Commented Jul 8, 2016 at 8:20
  • But if lookup for Upsert is not a best pratice, why a lot of articles use it ? Commented Jul 8, 2016 at 8:25
  • @BKChedlia where does it say is not a best practice? Can't see why Commented Jul 8, 2016 at 8:45

1 Answer 1

Reset to default
1

If your source(staging) and destination tables on the same Server you can use MERGE statement with Execute SQL task, which is faster and very effective than a lookup which uses a row by row operation.

But if the destination is on a different Server, you have the following options

  1. Use lookup to update the matching rows with an OLEDB Command(UPDATE Statement)
  2. Use a Merge Join (with LEFT OUTER JOIN) to identify the new/matching records and then use a conditional split to INSERT or UPDATE records. This works same as the Lookup but faster.
  3. Create a temporary table in the destination db, dump data from staging to that table and then use the MERGE statement, this is faster than using a lookup.
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I tried this Merge (above, I edit my post) but I have this error message : [Execute SQL Task] Error: Executing the query "MERGE INTO [PWCGFA_BG].[dbo].[Bank_Guar..." failed with the following error: "Incorrect syntax near 'bg'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Change your statement from WHEN NOT MATCHED BY bg THEN to WHEN NOT MATCHED BY TARGET THEN and WHEN NOT MATCHED BY stgbg THEN to WHEN NOT MATCHED BY SOURCE THEN, You need to use the keywords TARGET and SOURCE not the aliases, hope this helps
thanks, I did it, when I delete row then add new row and edit row , I have another problem : The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
That is because you might have IATA_CODE multiple times in the target table, probably you need to add additional conditions to match only one target row.
You are right, I discovered wrong data no unik id in csv file... thankx MSBI-Geek

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.