title: Merge of large tables fills transaction log
body: <p>Merge of large tables fills transaction log.
  Our ETL uses MERGE to import data from hospital information system.  When number of records exceeds several million the transaction log fills.  How can I restructure the statement so that the impact on transaction log is minimized?  Top()?  Openrowset?</p>
  
  <p>Our query is built dynamically in SSIS using variables.  Here is what it looks like:
  --Man query concatenates Mergequery1 to 5.  All are shown below.
  --Main query</p>
  
  <pre><code>begin transaction;
  
  begin try
  
  
  
  declare @MergeQuery varchar(max)
  
  set @MergeQuery = convert(varchar(max), @MergeQuery1) +  convert(varchar(max), @MergeQuery2)
  + ' ' + convert(varchar(max), @MergeQuery3)
  + ' ' + convert(varchar(max), @MergeQuery4)
  + ' ' + convert(varchar(max), @MergeQuery5);
  
  
  exec(@MergeQuery);
  
  end try
  
  begin catch
  
   declare
    @Message VARCHAR(4000)
    ,@Severity INT
    ,@State  INT;
  
   select
    @Message = ERROR_MESSAGE()
    ,@Severity = ERROR_SEVERITY()
    ,@State = ERROR_STATE();
  
   if @@TRANCOUNT &gt; 0
    rollback transaction;
  
   raiserror(@Message, @Severity, @State);
  
  end catch;
  
  if @@trancount &gt; 0
  begin
  
   commit transaction;
  
  
  end
  
  
  
  --Mergequery1  section modified
  "begin transaction
  
   begin try
    begin
  
     declare @ChangeSum table(change varchar(25));
     declare @Inserted int = 0;
     declare @Updated int = 0;
  
     merge top (1000000) into "+ @[User::VISNDestinationDatabase] +"." + @[User::DataDestinationTable]   +  " prod
     using " + @[User::VISNStagingDestinationTable]  + " stag
     on " + @[User::MergeOnResult] +
  
    "  when MATCHED then
     update set " +@[User::MergeUpdTextResult]   
  
  --Mergequery2
  @[User::MergeUpdTextResult2]  + " when not matched by target then"
  
  --Mergequery3
  "   insert
       ( " + @[User::MergeInsertResult] + 
    "       
       )"`enter code here`
  
  --Mergequery4
    "values
       ( "
        + @[User::MergeValueResult]+
    "   )"
  
  --Mergequery5
  "output $action into @ChangeSum;
  
     set @Inserted = (select count(*) from @ChangeSum where change = 'INSERT');
     set @Updated = (select count(*) from @ChangeSum where change = 'UPDATE');
  
   select @Inserted as Inserted, @Updated as Updated;
  
    end
   end try
  
   begin catch
    rollback
   end catch
  
  if @@trancount &gt; 0
  commit transaction"
  </code></pre>