UPDATE/INSERT data import query

[tinyevil777]

Afternoon all.

I've got an interesting one for you...

I have a product table, which i want to update using either SQL query, store procedure, or the SSIS import routine.


ProductTbl

ProductCode XRef
ABC ABC123
XYZ XYZ123
MNO MNO123

I have an excel document, containing new cross references (xref) which i want to use to update the ProductTbl.


Excel document

ProductCode XRef

ABC ABC123
XYZ XXX999
MNO OOO111
RST RST321

Scenario 1: Where the product codes match, and the Xref's match, do nothing. This is because there is no change necessary. (Example ProductCode: ABC)
Scenario 2: Where the product codes match, and the Xref's are different, update the Xref field in ProductTbl using the data in the Excel Document. (Example ProductCode: XYZ & MNO)
Scenario 3: Where the product code in the Excel Document is not in the ProductTbl, append that record to the ProductTbl (Example ProductCode: RST)

I've rattled my brain thinking about how i can do this, a store procedure seems like the obvious choice, as it caters for the use of an 'if statement'. However... i've never written a store procedure, and wouldn't know where to start.

There may even be something to already caters for this, but to my knowledge there isn't, therefore i'd greatly appreciate someones input, suggestion or advice on this matter.

Thanks alot in advance!

Tom

 

[bparkinson]

Stored procedure is the way to go. You will need to buy a SQL Server book and learn how to use it unless you have a DBA who can handle the back end for you.

 

[bparkinson]

Also, the learning curve for creating SSIS packages is even steeper, and without a deep understanding of T-SQL, views, stored procedures, and SQL Server in general is probably insurmountable, so scratch that approach for now.

 

[tinyevil777]

Thanks for the advice bparkinson. I've done abit more digging, and it seems that i can use the MERGE statement.

I think i'll try and build my statement, then try to place it in a Store Procedure.

If you have any suggestions or tips, then please feel free to chip in. Otherwise, thanks for your help!

 

[bparkinson]

Yes, MERGE was created just for this type of work.