UPSERT in SSIS

I had a work on a quick delivery which involves UPSET using SSIS, following is refresh!

What is UPSERT in typical scenario?
When I have to insert records into destination if the record does not exist and/or update multiple values of the record if the record exists (comparison to be made based on primary key)

What is the best practice in real scenario (Table is huge but delta is small)?
• Best available option is to load the delta values in a temporary table of same database and execute SQL Command or procedure using MERGE statement

Sample demo for Merge statement usage:

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.Source(EmployeeID int, EmployeeName varchar(10), 
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(103, 'oldBob');

GO
INSERT dbo.Source(EmployeeID, EmployeeName) Values(103, 'Bob');
INSERT dbo.Source(EmployeeID, EmployeeName) Values(104, 'Steve');
GO


select 'Before Update' as WhenSelected, * from source 
select 'Before Update' as WhenSelected,* from target


USE tempdb;
GO
MERGE Target AS T
USING Source AS S
ON (T.EmployeeID = S.EmployeeID) 
WHEN NOT MATCHED BY TARGET --AND S.EmployeeName <> 'Bob' 
    THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED 
    THEN UPDATE SET T.EmployeeName = S.EmployeeName
----WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
----    THEN DELETE 
OUTPUT $action, inserted.*, deleted.*;
GO

select 'After Update' as WhenSelected,* from source 
select 'After Update' as WhenSelected,* from target

drop table source 
drop table target 
--
--
-- SQL 2005 does not allow MERGE function, you may need to write manual Update/Insert sattement
-- UPSERT in SQL 2005 (UPSERT in SQL Server 2005
 UPDATE dest SET 
dest.adddate=src.adddate, dest.addtime=src.addtime, dest.adduser=src.link, dest.adduser=src.link -- All Coluimns
  FROM DestinationDB..TODO AS dest
  INNER JOIN sourceDB..TODO_delta AS src
  ON dest.link = src.link;

INSERT DestinationDB..TODO(adddate ,addtime ,adduser, link) -- All columns
 SELECT adddate ,addtime ,adduser, link -- All columns
 FROM sourceDB..TODO_delta AS src
 WHERE NOT EXISTS (SELECT 1 FROM DestinationDB..TODO WHERE link = src.link);
--
--

We also have traditional option to UPSERT using SSIS Lookup option but is not recommended due to performance (Lookup will pick one row at a time and process). I will cover this step by step in next blog!

Advertisements
This entry was posted in Productivity, SQL, SSIS and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s