Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #576

Re: how can I copy a huge table to another database?

Show all headers | View raw

Hardy (wyhang@gmail.com) writes:
> suppose I have 100M records and we don't like INSERT.
> 
> one option is to export to external file and bulk load the file.
> 
> is there any better idea? linked server configured here.
 
Since you mention linked server, I assume that the tables are not
only in different databases, but also linked servers? In that case
I would do BCP (since I don't know SSIS).

If they are on the same server, you should absolute use INSERT if you
are on SQL 2008, since INSERT can be minimally logged on SQL 2008, 
provided this is true:

*  Database is in simple or bulk-recovery.
*  The table is a heap.
*  You specify the TABLOCK hint.

Books Online has the full details.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

how can I copy a huge table to another database? Hardy <wyhang@gmail.com> - 2011-07-27 08:57 -0700
  Re: how can I copy a huge table to another database? "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-07-27 14:27 -0400
  Re: how can I copy a huge table to another database? "Fred." <ghrno-google@yahoo.com> - 2011-07-27 13:45 -0700
  Re: how can I copy a huge table to another database? Erland Sommarskog <esquel@sommarskog.se> - 2011-07-27 23:53 +0200

csiph-web