CODEWITHSUNDEEP
sql-serversql-server-2008ssisupsert
Raj More
Raj More

Reputation: 48016

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

Upvotes: 7

Views: 27392

Answers (6)

Bidy Ray
Bidy Ray

Reputation: 21

We can use slowly changing dimension component in SSIS to upsert.

Guide on usage.

Upvotes: 0

wp78de
wp78de

Reputation: 18950

Apart from T-SQL based solutions (and this is not even tagged as /), you can use an SSIS Data Flow Task with a Merge Join as described here (and elsewhere).

enter image description here

The crucial part is the Full Outer Join in the Merger Join (if you only want to insert/update and not delete a Left Outer Join works as well) of your sorted sources.

enter image description here

followed by a Conditional Split to know what to do next: Insert into the destination (which is also my source here), update it (via SQL Command), or delete from it (again via SQL Command).

  1. INSERT: If the gid is found only on the source (left)
  2. UPDATE If the gid exists on both the source and destination
  3. DELETE: If the gid is not found in the source but exists in the destination (right)

enter image description here

Upvotes: 8

Raj
Raj

Reputation: 10843

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.

Upvotes: 2

Kip Real
Kip Real

Reputation: 3459

I would use the 'slow changing dimension' task

Upvotes: -1

Jonathan Veiler
Jonathan Veiler

Reputation: 89

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.

Upvotes: 0

David Benham
David Benham

Reputation: 1174

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))

Upvotes: 2

Related Questions