UPSERT in SSIS

Question

I am writing an SSIS package to run on SQL Server 2008. How do you do an UPSERT in SSIS?

IF KEY NOT EXISTS
  INSERT
ELSE
  IF DATA CHANGED
    UPDATE
  ENDIF
ENDIF

Answer 1

Hi

I would suggest you to have a look at Mat Stephen's weblog on SQL Server's upsert.

http://209.34.241.68/mat_stephen/archive/2005/08/31/410022.aspx

cheers

Answer 2

The basic Data Manipulation Language (DML) commands that have been in use over the years are Update, Insert and Delete. They do exactly what you expect: Insert adds new records, Update modifies existing records and Delete removes records.

UPSERT statement modifies existing records, if a records is not present it INSERTS new records. The functionality of UPSERT statment can be acheived by two new set of TSQL operators. These are the two new ones

EXCEPT
INTERSECT

Except:-

Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query

Intersect:- Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

Example:- Lets say we have two tables Table 1 and Table 2

Table_1 column name(Number, datatype int)
----------

1
2

3
4
5

Table_2 column name(Number, datatype int)
----------

1
2

5

SELECT * FROM TABLE_1 EXCEPT SELECT * FROM  TABLE_2

will return 3,4 as it is present in Table_1 not in Table_2

SELECT * FROM TABLE_1 INTERSECT SELECT * FROM  TABLE_2

will return 1,2,5 as they are present in both tables Table_1 and Table_2.

All the pains of Complex joins are now eliminated :-)

To use this functionality in SSIS, all you need to do add an "Execute SQL" task and put the code in there.

Raj

Answer 3

See SQL Server 2008 - Using Merge From SSIS. I've implemented something like this, and it was very easy. Just using the BOL page Inserting, Updating, and Deleting Data using MERGE was enough to get me going.