InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 19:40:29 GMT20http://www.sqlservercentral.com/Forums/Topic1429268-364-1.aspxHi Rocky´sI totally agree with that, the use of the merge command is for me the better option. I designed several ETL packages the last year and I tried the 3 options, and at the end I always used the Merge command.Here I leave you a code snippet from my personal library. This code makes the following:- Log the start time and name of the package in a custom log table with an identity column- Merge into a target table the results of a query (as a source). You can adapt the source query to your needs- The columns that match the merge condition are updated, if not, they are inserted.- At the end the finish time is log together with the row count of updated and inserted records.[code="sql"]-- I use a table to log the start and finish time, package name, number of rows inserted and number of rows updatedinsert into SSIS_Log (PackageName,DTStart) values (?,GetDate()); -- Map the System variable "PackageName"declare @mergeResultsTable table (MergeAction VARCHAR(20)); declare @insertCount int, @updateCount int;MERGE Table_A AS TARGETUSING (SELECT Col1 ,Col2 ..... ,ColNFROM Table_B) AS SOURCE ON (TARGET.Key1 = SOURCE.Key1 and TARGET.Key_2 = SOURCE.Key_2)-- UpdateWHEN MATCHED THEN UPDATE SET TARGET.Col1= SOURCE.Col1 ,TARGET.Col2= SOURCE.Col2 ,TARGET.UpdateDT=GetDate() -- I create a UpdateDT column in the target table to store the update datetime -- etc--InsertWHEN NOT MATCHED BY TARGET THEN INSERT( Col1 ,Col2 ,CreateDT -- I create a CreateDT column in the target table to store the insert (or creation) datetime -- etc)VALUES( SOURCE.Col1 ,SOURCE.Col2 ,GetDate() -- etc)OUTPUT $action into @mergeResultsTable; update SSIS_Logset UpdatedCount = -- Log the count of updated rows(SELECT COUNT(*) FROM @mergeResultsTable WHERE MergeAction = 'UPDATE'), InsertedCount = -- Log the count of inserted rows(SELECT COUNT(*) FROM @mergeResultsTable WHERE MergeAction = 'INSERT'),DTFinish = GetDate()where Idx = ( select MAX(Idx) from SSIS_Log );[/code]If your target table should be treated as a SCD I strongly recommend to you the following article by Adam Aspin:[url=http://www.sqlservercentral.com/articles/MERGE/73805/]Slowly changing dimensions using T-SQL MERGE[/url]If you have further question just write me.Kind RegardsTue, 12 Mar 2013 03:04:40 GMTPaul Hernándezhttp://www.sqlservercentral.com/Forums/Topic1429268-364-1.aspxUse the Merge, of those 3.Mon, 11 Mar 2013 15:12:42 GMTtmitchelarhttp://www.sqlservercentral.com/Forums/Topic1429268-364-1.aspxHi,I am new to SSIS.I have to load 50 millions of records(full load) into a SQL Server table.After the full load I have to create another package for incremental load based on Hash value.Please suggest me one of the below mentioned approach to get good performance.-Merge command-Look Up-SCDThanks in advanceMon, 11 Mar 2013 07:41:33 GMTRocky's