More Efficient Way to Copy Large Data from One Table to Another

Question

We have a few tables with over 300,000 records and it takes several hours for records to be transferred/copied from one table to another table. Is there a more efficient effective way than using cursors and copying each record one by one from one table to another table, when dealing with large quantities of data?

Code:

open SOMETBL 
fetch SOMETBL into @key1,@key2,@key3,@key4
while(@@fetch_status = 0)
 begin

  SELECT @key1InMapping = count(*) FROM SOMEOTHERDB.dbo.tblSOMETBLping WHERE fldEServicesKey = @key1

  SELECT @eServiceTypeKey = fldAServiceTypeKey FROM SOMEOTHERDB.dbo.tblAServiceType WHERE fldAServiceTypeNumber = @key4

 if (@eServiceTypeKey=null or @eServiceTypeKey=0)
           set @eServiceTypeKey = 50

  if @key1InMapping>0
  begin
    update SOMEOTHERDB.dbo.tblSOMETBLping set fldAServiceTypeKey=@eServiceTypeKey where fldEServicesKey= @key1
   -- print 'post='+convert(varchar,@key2) + ' :key1='+convert(varchar,@key1)+ ' :serviceTypeKey='+convert(varchar,@eServiceTypeKey)+' : serviceTypeNum='+convert(varchar,@key4)
  end
  fetch SOMETBL into @key1,@key2,@key3,@key4 

end

close SOMETBL

It's the cursors that are making this take several hours. A straight SQL query will take several seconds to several minutes to copy only 300,000 rows. — HardCode, Sep 06 '13 at 17:07
Is the data transfer happening in a single Database or are you moving data between databases? — Declan_K, Sep 06 '13 at 17:18
Often, the best way to move data from one table to another is to not do it at all. Simply use the data in the first table. — Dan Bracuk, Sep 06 '13 at 17:57

score 4 · Accepted Answer · answered Sep 06 '13 at 17:24

300,000 records is a small amount of data in database terms. But it is too large to be using cursors which really should not be used for more than a couple of hundred records and frankly once you learn to write set-based code, it is generally shorter and takes less time to write than a cursor anyway. So I would never use a cursor as a first choice, it is a technique of last resort. You should not be thinking about inserting/updating or deleting one record at a time in a cursor. You use set-based operations. Now with 300,000 records you might want to consider a combination of set-based and cursoring where you process a group of recorss at a time (say 10,000) rather than cursor one a time.

Checke out the following for details on how to change a cursor to set-based code. http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

Thanks for this response. I shall review the link you shared. After running into our issue, I did have a feeling that using cursor should not be the first thing to use but was not knowledgeable enough to know what other solutions could be used. — that_developer, Sep 06 '13 at 17:42

More Efficient Way to Copy Large Data from One Table to Another

1 Answers1