One of the things we discovered was that we had an unnecessary join. More importantly it was a cross database join which was killing the query. This modified query eliminates the cross database join and keeps all joins within the same database but we still have to join to a "wild card" table which is only known at runtime. The results are still pretty impressive where we can copy 10 mil recs in an average time of 35 seconds.

So in the effort keeping our log entry to a min we were experimenting with these two options but we're not understanding how is sql treating these any different or same for that matter? More importantly what is it doing with the logs? Any idea? Thank in advance.

System.Data.SqlClient.SqlBulkCopy

SELECT z.*, 
      case when mmd.DateOrig < ^AgeDate^ then 1 else 0 end as ArchiveState, 
      GETDATE() AS ArchiveDate
   FROM msb INNER JOIN mmm on mmm.MatchSetId = msb.MatchSetId and mmm.DisplayCol = msb.DisplayCol
      INNER JOIN mmd on mmm.MatchmadeId = mmd.MatchmadeId
      INNER JOIN ProductionTable z on z.Match_TxnId = mmm.TxnId
   WHERE msb.MatchSetId = 3
      AND msb.DisplayCol = 1

** Versus **

T-Sql:

SELECT z.*, 
      case when mmd.DateOrig < ^AgeDate^ then 1 else 0 end as ArchiveState, 
      GETDATE() AS ArchiveDate into StagingTable
   FROM msb INNER JOIN mmm on mmm.MatchSetId = msb.MatchSetId and mmm.DisplayCol = msb.DisplayCol
      INNER JOIN mmd on mmm.MatchmadeId = mmd.MatchmadeId
      INNER JOIN ProductionTable z on z.Match_TxnId = mmm.TxnId
   WHERE msb.MatchSetId = 3
      AND msb.DisplayCol = 1