We had in place a daily process where we imported item information from SAP into an Items table in a SQL 2005 database.  The SAP information was placed in a .txt file and then bulk inserted into the table using the T-SQL Bulk Insert command.

We were then asked to modify this process.  Instead of completely overwriting the Items table in the database, we needed to merge the SAP item information into the Items table.  That is, we needed to update and insert rows in the Items table using the SAP .txt file as input.

If the database was an SQL 2008 database, we would have used the Merge command to merge the information; however, we were using SQL 2005.  Below are the steps and T-SQL I created to simulate the Merge command.
 

1)      Created a replica of the Items table called Load_Items

2)      Ran the following T-SQL

-- Load SAP Item information into Load_Items table with Bulk Insert command.

Truncate table Load_Items  

BULK INSERT Load_Items
FROM  '\\servername\SAP_Items.txt'
WITH (FORMATFILE = ‘\\servername\SAP_Items.FMT')

  /*
   Merge updated and new Item information into Items table by deleting rows in the Items table that have the same key value (Item_Code) as that
   in the Load_Items table and then insert those rows back (update) plus any new rows (insert) from the Load_Items table.
*/

Delete from Items
Where Item_Code In (Select Item_Code from Load_Items)

Insert Into Items
Select * from Load_Items