I am trying to speed up the copying of a large dataset from a table in one database into an empty table of same structure on another database on a different server (both are sql 2005). Both server instances are linked correctly, and can communicate with each other. Using code Insert into table1 select * from table2 I'm pretty new in the DBA arena. I read that you can only do bulk insert from flat files. I would like to perform the same type if insert (bulk copy) from one table to another. Logging isn't an issue for the destination database. I also read in help that you could use table hints to mimic bulk insert, but have found no examples. ANY help would be appreciated. biggest table has 214 million+ records. Thank you in advance, Vicki
You can try THREE ways... 1. Create an SSIS package and configure to use bulk load option in it. 2. Create a linked server from serverA to ServerB and DROP table at the destination and use SELECT * INTO TABLENAME FROM SERVERNAME.DBNAME..TABLENAME 3. http://davidhayden.com/blog/dave/archive/2006/01/13/2692.aspx
There are even more ways. I'm a commandline guy and I like prompts. =;o) To make it simple, one can divide it into three steps. Depending on sizes, network speeds and time-requirements, it's possible to change things around. (for example, it may be benificial to zip a file before copying over the network, if the file is large enough) 1) bcp out on server A to local disk, optionally pre-sort the data in the order of the clustered index. 2) copy the file to server B 3) bcp in on server B - the destination table should already exist, and also have a clustered index in place. Most of the times, having an exisiting clustered index yields higher throughput than bulking into a heap. /Kenneth