Post reply

Copy a table structure and data in SQL SERVER

  • August 28, 2008 at 7:53 am

    #99253

    hi,

    I whant to copy a table whit the structure and the dependencies, im using sql server 2005.

    thank you for any help.

  • August 28, 2008 at 8:53 am

    #864475

    Hi there,

    the easiest way would be to backup the database where this table is and restore it on the target system. You then have an exact copy of the table structure including data. You can then remove any tables that you don't need. That way all logical dependancies are there without having to think about them.

    You could try doing data migration with SSIS / DTS / BCP or Linked Server connections, but these will probably take longer than my first suggestion.

    regards

    GermanDBA

    Regards,

    WilliamD

  • August 28, 2008 at 9:49 am

    #864528

    GermanDBA (8/28/2008)

    ...the easiest way would be to backup the database where this table is and restore it on the target system. You then have an exact copy of the table structure including data. You can then remove any tables that you don't need.

    :w00t: What?! You never seen a multi-tera byte environment, didn't you? 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • August 28, 2008 at 9:55 am

    #864531

    Hi Paul,

    I do work with multi-terabyte systems. That was not mentioned in the original post, so I started with the basic possibilities. I would imagine that the methods would be carefully considered before being used.

    regards

    GermanDBA

    Regards,

    WilliamD

  • August 28, 2008 at 10:22 am

    #864551

    Use SSMS, navigate to the database, expand the database, right click on the table you wish to script, then select "Script table as", select "Create", then select one of these options "New query editor window", or File or Clipboard" I would suggest at first use the "New query editor window". This should give you the create table script including "Objects on which the table depends". It will not give you "objects that depend on your table". To view these go back to right click on the table you wish to script, and then select "View dependencies", in the next window select "objects that depend on table" to see those.

    Use the script to create your new table and then transfer data from original table to new database with the new table using normal methods.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • August 28, 2008 at 1:18 pm

    #864654

    thank you!! bitbucket and GermanDBA

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

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