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.

Thanks.

Hardy wrote:

There's always bcp, dts, ssis.
Depending on the version, a dts or ssis package can be generated using the
Import/Export wizard.

On Jul 27, 11:57*am, Hardy <wyh... (AT) gmail (DOT) com> wrote:
We have a weekly transfer of about 5M fairly wide records (about 4GB
total with indexes) . We just use an SSIS transfer accepting whatever
the undelying method is. We found that a big saving in dropping
indexes in the target table and rebuilding them aftert the transfer.
In our system this seems to take around 11 minutes over a 100MHZ
network including the index rebuild.

Although the only transfer on your scale I've done was multiple
complete databases via backup, file copy and restore, I found that
setting up a dedicated GHZ link between the servers was well worth the
set-up time. I was able to on the order of 75GB of databases in
around 90 minutes.

Fred.

Hardy (wyhang (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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