Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Copy a Table

  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Unanswered: Copy a Table

    In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

    Copy is in the popup menu if you right click on a table but there's no Paste

  2. #2
    Join Date
    Sep 2003
    Posts
    2

    Unhappy Re: Copy a Table

    Originally posted by wey97
    In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

    Copy is in the popup menu if you right click on a table but there's no Paste

  3. #3
    Join Date
    Aug 2002
    Location
    Southern California
    Posts
    35

    Re: Copy a Table

    [QUOTE][SIZE=1]Originally posted by wey97
    In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

    You can do this:

    SELECT *
    INTO B
    FROM A

    Notes:
    B = TABLE NAME of COPY
    A = TABLE NAME of ORIGINAL

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Copy a Table

    Originally posted by wey97
    In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

    Copy is in the popup menu if you right click on a table but there's no Paste
    You can do paste in QA (shift+Insert or Cntrl+V)

  5. #5
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    To copy only the structure with no data (minimally logged)

    select * into newtable from oldtable where 0=1

    To copy only the structure with data (minimally logged)

    select * into newtable from oldtable

    To copy data into an existing table (Fully Logged)

    insert into newtable select * from oldtable

    To Copy data into an existing table where it doesn't already exist (logged)

    insert into newtable select * from oldtable where PK not in (select * from newtable)

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    rhigdon: don't do NOT IN, you'll kill everybody else and possibly the server as well. do left outer join on key_field where key_field is null

  7. #7
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    I've heard that before, I'll have to test the IO difference. Have you tested it?
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  8. #8
    Join Date
    Feb 2003
    Location
    @ home
    Posts
    163

    Re: Copy a Table

    Right click the database then alltasks->export data
    A wizard will appear...
    Select the table, put the correct source and destination and at the end choose to drop the existing destination object and not append the data.

    It's easy and simple. You can also select to mantain permissions, indexes, etc

    Paulo


    Originally posted by wey97
    In SQL Server 2000, what is the easiest way to make an exact copy of an existing table?

    Copy is in the popup menu if you right click on a table but there's no Paste

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    yup, many times, plus evidence presented by others, plus ms white papers.

  10. #10
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    I'm pretty sure you are right, going to do some testing but if you could share any of those white papers I would appreciate it.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, you can't use:

    where PK not in (select * from newtable)

    anyway.

    I'm sure you meant either:
    where PK not in (select PK from newtable)
    or:
    where not exists (select * from oldtable where old.PK = new.PK)

    ...both of which generate the same optimizer plan, and oddly enough they ran slightly (insignificantly even) faster than the left outer join method, which threw in an extra step for filtering. Maybe because my test table only had about 20,000 rows.

    I've use all three methods and never killed a database with them, but I would be interested in reading any MS white papers as well.

    blindman

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I believe you are right, Blindman. MS has been tinkering with this part of the query optimizer, and I think the query plans are largely the same. Imagine my surprise when I was about to storm into a developer's cube to have him re-write such a query, when my own re-write did nothing to help.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, the left join method did use a slightly different plan than the other two, so it could potentially be more (or less) efficient for larger tables. If anybody feels the urge to experiment please post the results.

    blindman

  14. #14
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Yes BM, I meant

    where PK not in (select PK from newtable)


    Well, I am a little confused as I rewrote a query that did a NOT IN query to a 1.4 million row table from a 1000 row table and it cost me a total of 3014 logical reads, when I rewrote it in a left outer join it cost me 3126 logical reads so either I am doing something wrong or the difference is insignificant.

    I have searched pretty hard but have been unable to find any MS whitepapers discussing this (although I did find an interesting book in my search that I have now ordered!)
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  15. #15
    Join Date
    Sep 2003
    Posts
    522
    white papers, articles...this issue goes all the way to 6.0 times where non-clustered field was challenged with IN/NOT IN and the optimizer would revert to table scan in both outer and inner queries. it is possible that in the case of the PK, unless it is non-clustered, not in and let outer join would yield the same result, or close to be the same. this is not the point. the point is that IN clause is less preferrable than exists or left outer join. any more requests for articles?

Posting Permissions

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