Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: how can I copy a huge table to another database? Date: Wed, 27 Jul 2011 23:53:15 +0200 Organization: Erland Sommarskog Lines: 29 Message-ID: References: <84557c50-c2f4-43e7-bd33-3972e14ac737@g5g2000prn.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="24788"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX19kqITL+Xk0RilS8viQh7sU" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:G/Wy6+B2BhYKDTUhY7jbuJDoquM= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:576 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