Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Question Unanswered: What is quickest way to copy a table...

    I currently use
    SELECT * INTO newtable FROM oldtable
    this works fine for on small tables but now i am having to deal with larger files.
    +1,000,000 rows and 1021 columns. Any ideas on a quicker way to do a copy.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by hicpics
    I currently use
    SELECT * INTO newtable FROM oldtable
    this works fine for on small tables but now i am having to deal with larger files.
    +1,000,000 rows and 1021 columns. Any ideas on a quicker way to do a copy.

    The quickest way is to not replicate the data, other than that, there is no quicker way.

    Can you tell us what you're doing and why?
    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
    Mar 2005
    Posts
    71
    We are using a 4GL to access SQL Tables. COBOL is our language. Some of our customers use the proprietary file structure and others use the SQL. We use a COBOL program to basically copy the old file to a backup file. then destroy the old file, create a new one then read the backup file and load the records into our new file.
    copy A to B
    destory A
    create new A
    copy B to A
    using a COBOL program we can execute SQL commands thus the INSERT * INTO ....
    we looked into creating/using a DTS package but it didnt look to promising since we have a bad habit of doing minor changes to a table after we release a version.

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    u should consider renaming table name using sp_rename.

    eg:Copy A to B can be accomplish by following query
    exec sp_rename 'A' to 'B'
    this will destroy table A and records will be in B.

    NB:What u mean by create new A ,change in table structure of table A?




    hope this will help u
    cheers

Posting Permissions

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