How to copy a table structure and data to a different server?

  • I am wondering if there is a simple command I can run to copy a table structure and its data from one SQL server to another. I am connected to both servers in SSMS and I know I can do a select into to do this to a different database on the same server but is something like this possible if i want to create the table on another server?

  • You can use the Export wizard?

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn

  • keeping it simple - right click on database - tasks - export data

    ---------------------------------------------------------------------

  • thanks for the replies - that would definitely be nice and easy but unfortunately i dont have that option when I right click.

  • is it there but greyed out?

    what permissions do you have?

    ---------------------------------------------------------------------

  • no its just not there. I think I have the free version of the management studio.

  • there is a very handy free plug in for SSMS here:

    http://www.ssmsToolsPack.com

    one of the options is to right click on a table in Object Explorer>>SSMS Tools>>Generate Insert Statements

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • hey thanks for the tip. I did download and install that and it looks like it will generate the script to copy the data over but it does not create the script to actually create the table structure. any ideas for that?

  • Blair Dee-474691 (4/29/2013)


    hey thanks for the tip. I did download and install that and it looks like it will generate the script to copy the data over but it does not create the script to actually create the table structure. any ideas for that?

    ok that part is the easiest: Scripting the DDL for any object is already built into SSMS:

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I tried that before and it kept giving me an error when I ran the create code on the new server. For some reason I had to remove the bit that created the PK. Once I did that it was fine. THen I just went into design mode and created the PK where it should be.

    Thanks for the tips.

  • The script that generates out as individual INSERT scripts is going to be comparatively and, maybe, horribly slow. With that thought in mind, I recommend doing the right click thing to generate the script for the table creation and the use of BCP in the "native" mode to export the data to a disk and then import it using either BCP or BULK INSERT on the other server.

    If this is something that needs to be done on a regular basis, consider one form of replication or another.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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