Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Question Unanswered: copying tables in SQL SERVER

    I have a 100m row table that I need to come from one database to another database in SQL SERVER.

    The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.

    Right now I am doing a
    SELECT *
    INTO
    (table name)
    FROM (table name)

    and on a 100m row table it is taking around 52 hours. Not acceptable.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is it on the same server?

    bcp out in native format and bcp in is probably the fastest

    But the SELECT * INTO is a minimally logged operation....and if it's on the same server....

    I'm not so sure bcp would beat it since it's 1 operation as compared to 2.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    Yes it is on the same server.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    For 100 million rows, I think I'd partition it up anyway...

    What's the DDL of the table...is it a heap or does it have a pk?

    If it's got something unique, I'd split it up in to 10 tables and thread out the SELECT Collist INTO Table1
    SELECT Collist INTO Table2
    SELECT Collist INTO Table3
    SELECT Collist INTO Table4
    ect

    And run all 10 at the same time from 10 separate osql bat files...

    thats 1 select per file.....

    How long does a backup take?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2004
    Posts
    5

    copying tables in SQL SERVER

    Hello Everyone,

    If you would like to copy records within the same database between the different databases the you can use this query,

    insert into <table Name> select * from <Destination Table>

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jyothi_priya
    insert into <table Name> select * from <Destination Table>
    The only problem is that this syntax is fully logged, while the SELECT INTO syntax is only minimally logged. Since the SELECT INTO appears to be too slow and I'd expect this to be even slower, I don't think it would be a good solution.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn...I wonder if that belongs here...

    http://thedailywtf.com/archive/2004/09/01/1511.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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