Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2002
    Posts
    23

    Unanswered: How to copy table in SQL Server 2000?

    I want to make an exact copy of a table in SQL Server 2000.
    If I right click on the table I can select copy, but paste does not show up?

  2. #2
    Join Date
    Jan 2005
    Location
    Australia
    Posts
    5
    Hi

    You're probably better off right clicking the database itself (in Enterprise Manager), and selecting All Tasks | Import Data. Even though the data source and destination are the same, you can make it copy to a table with a new name. When you get to the list of available tables, check the one you want then alter the destination to a new name - click on transform to get an idea of what's going on.
    I find that I need to setup my identities and keys again, but otherwise works well.

    An alternative would be to copy the table as you've done, then use the above to pull data into it (or an insert statement).

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    How about just "select * into new_table from old_table"?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Dec 2004
    Posts
    47
    rd is exactly right that syntax would do... 'Select * INTO'

  5. #5
    Join Date
    Jun 2002
    Posts
    23
    I was able to do it that way. I was wondering if there is an easy way to just copy and paste - similar to how you would do it in access.
    I guess I don't understand why you can copy a table but not paste it.

  6. #6
    Join Date
    Jan 2005
    Posts
    1
    When you right-click copy a table name in Enterprise Manager, you are only copying the table design. If you go to your Query Analyzer window and paste, you will see the create table statement for that table. This is functionally equivalent to right-clicking on the table in the Query Analyzer object browser and choosing "Script object to Clipboard As >> Create."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Be aware that "select * into new_table from old_table" does not make an exact copy of the TABLE. It makes an exact copy of the DATA. Any indexes, foreign keys, or triggers associate with the table will not be created.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i think the effective question is the one we always end up asking

    What do you want to copy a table for ?
    or
    what are you trying to do and maybe we can help you find an easier way to do it.

Posting Permissions

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