SQL Server Performance

Bulk Insert from one SQL table to Another Sql table

Discussion in 'SQL Server 2005 General DBA Questions' started by vaversa, May 28, 2008.

  1. vaversa New Member

    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
  2. MohammedU New Member

    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
  3. Kewin New Member

    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

Share This Page