UPSERT in SSIS

问题:

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

回答1:

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.

回答2:

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

SQL 2005 - UPSERT: In nature but not by name; but at last!

回答3:

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.

回答4:

Another way to create an upsert in sql (if you have pre-stage or stage tables):

--Insert Portion
INSERT INTO FinalTable
( Colums )
SELECT T.TempColumns
FROM TempTable T
WHERE
(
    SELECT 'Bam'
    FROM FinalTable F
    WHERE F.Key(s) = T.Key(s)
) IS NULL

--Update Portion
UPDATE FinalTable
SET NonKeyColumn(s) = T.TempNonKeyColumn(s)
FROM TempTable T
WHERE FinalTable.Key(s) = T.Key(s)
    AND CHECKSUM(FinalTable.NonKeyColumn(s)) <> CHECKSUM(T.NonKeyColumn(s))

回答5:

I usually prefer to let SSIS engine to manage delta merge. Only new items are inserted and changed are updated. If your destination Server does not have enough resources to manage heavy query, this method allow to use resources of your SSIS server.

回答6:

I would use the 'slow changing dimension' task