how to handle the Specify how to handle rows with no matching entries for Insert/Update

Question

Hi,

How do we handle the Insert of new records and updating the existing records in there is a change from source, this is based on a ID. I have written this in T-SQL, but when implementing in SSIS using Lookup it's giving me error:

My T-SQL stmt:

Insert into dbo.table3 ([ColId]
      ,[Col1]
      ,[Col2]
      ,[Col3]
      ,[Col4]
      ,[Col5]
      ,Col6
     ,Col7)
SELECT x.* FROM 
(SELECT a.[ColId]
      ,a.[Col1]
      ,a.[Col2]
      ,a.[Col3]
      ,a.[Col4]
      ,a.[Col5]
        ,b.Col6
     ,b.Col7
FROM [dbo].[table1] a
JOIN dbo.table2 b ON a.ColID = b.ColID) X
LEFT JOIN dbo.table3 Y ON X.colID = Y.colID WHERE Y.colID Is NULL

UPDATE the table3 if there is any change in existing records:

UPDATE 
	b
SET
	b.col2 = a.col2,
	b.col3 = a.COl3,
	b.Col4 = a.col4,
	b.col5 = c.col5,
	b.col6 = c.col6

FROM dbo.table3 b
JOIN dbo.table1 a ON a.colID = b.colID
JOIN dbo.table2 c ON a.ColID = c.colID

My SSIS package presentation:

                 dbo.Table1 (OLEDB SOURCE)
                             |
dbo.Table2 (Lookup) (Which, Specify how to handle rows with no matching entries to be used? I'm using Redirect row to no match) 
                             |
                dbo.table3 (lookUp) (Which, Specify how to handle rows with no matching entries to be used?, my prcoess fails here)
                            |  (lookup match output)
                dbo.Conditionsplit
                             (newrecords and update records)
                            |
                            |
     Newrecords                          updaterecords (OLED Command)
   (table3 destionation)              (table3 update source)

 

Can any one let me know which Specify how to handle rows with no matching entries to be used  at table3 lookup ?

Answer 1

It will be faster to bulk load a staging table and then use TSQL MERGE to identify the existing rows and perform the inserts and updates.

David

---

David http://blogs.msdn.com/b/dbrowne/