Post reply

Copying 3 Million Rows

  • July 27, 2005 at 6:55 pm

    #69495

    Hi All

    I need to transfer 3 Million rows from Source to Destination SQL Servers. What is the fastest way to do it?

    Thanks

    Arun

     

  • July 28, 2005 at 12:06 am

    #577775

    Hi Arun,

    Is ur source server is in local location then the fastest way is create the table using linked server.

    Or u can use BCP Utility to transfer the data. U can get more information from BOL.

    hope this help u.

     

    from

    killer

  • July 28, 2005 at 11:13 am

    #577970

    Hi Killer

    Thanks for your input. It helped.

    Thanks

    Arun

     

     

  • July 29, 2005 at 10:09 am

    #578230

    Depends on what you mean by fastest! For you to develop or for the data to be transferred?

    Fastest to transfer is BULKING the data out to a file on the server where the data resides, compress it, send it over the network, uncompress it, BULK it in and there you go! Not that fast to develop but you are using the smallest amount of hardware resoures you can.

    Both DTS and Linked server approach is not compressing your data and if you are talking about 3M rows of 0-8kb (we are talking up to 24 Gbyte) over a slow network you will experience slowness on a level you didn't think existed!

    //Hans

  • July 30, 2005 at 4:01 am

    #578327

    Microsoft Windows [Version 5.2.3790]

    (C) Copyright 1985-2003 Microsoft Corp.

    C:\Documents and Settings\Administrator>cd..

    C:\Documents and Settings>cd..

    C:\>bcp bbc-nortal_d.tnt.empt out c:\shasha\empt.bcp -n -Utnt -Ptnt

    SQLState = 37000, NativeError = 170

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.

    C:\>bcp bbc-nortal_d.tnt.empt out c:\shasha\empt.bcp -n -Utnt -Ptnt -eC:\shashankt\error.txt

    SQLState = 37000, NativeError = 170

    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax

    near '-'.

    i tried but i got this type of error

    tnt is owner name and also password to connect database 'bbc-nortal_d'

    we have sql server 2000 enterprise edition

    please help me out

    T.I.A

    Shashank

     

     

    Regards,

    Papillon

  • August 1, 2005 at 3:00 am

    #578442

    I guess the problem might be with the characters that make up the table name. BCP maybe interprets part of it as a parameter. Try to do a

    'bcp "SELECT * FROM tnt.empt" queryout c:\shasha\empt.bcp -S"bbc-nortal_d" -n -Utnt -Ptnt'

    //Hans

  • October 19, 2005 at 4:05 pm

    #598512

    why dont use a tape backup ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply