Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    20

    Red face Unanswered: BCP - between 2 SQL Server Tables

    Any syntax where i will copy an sql server table to another table using BCP.im using sql server 2000 on vb.net, DTS isn't working for me because i want to edit some conditions on the package and it just dont work well.thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    are the 2 tables in the same database, or the same server or on the same network? you may be able to do this with a simple

    INSERT INTO MyTable(MyField)
    SELECT

    using fully qualified names and maybe a linked server.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you want to use bcp, you have to do it in 2 steps:

    1. use bcp with "out" or "queryout" keywords to go from source table to flat file
    2. use bcp with "in" keyword to go from flat file to dest table.

    if you have many millions of rows, bcp is faster than using INSERT...SELECT.

    read about bcp syntax here:

    http://msdn2.microsoft.com/en-us/library/ms162802.aspx

    Another option, since you are using .net, is to use the sqlbulkcopy class, which has the advantage of not needing to write the intermediate file. It's quite fast. Read up here: http://msdn2.microsoft.com/en-us/lib...lbulkcopy.aspx. I recommend you to use the overload of WriteToServer that takes IDataReader, not the DataTable overloads. much more memory efficient.
    Last edited by jezemine; 02-05-07 at 19:09.

  4. #4
    Join Date
    Aug 2005
    Posts
    20
    Thanks..maybe i forgot that one..workin on it now..hope it will be much faster

  5. #5
    Join Date
    Aug 2005
    Posts
    20
    thats what im thinking..i have to do two steps..good if im transferring it to other database or web.will it still be much faster than 'Insert...Select' statement in two steps?....

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it depends on how many rows you are moving. if it's millions, bcp/bulk insert/sqlbulkcopy will be faster than INSERT...SELECT.

    If you have lots of rows, I would try sqlbulkcopy. it's only one step, and uses the same underlying method as bcp, namely, bulk insert.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •