Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to copy a table structure and data to a different server? Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:15 PM
Points: 42, Visits: 50
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?
Post #1446094
Posted Wednesday, April 24, 2013 10:21 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:57 AM
Points: 704, Visits: 3,270
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

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
Post #1446109
Posted Wednesday, April 24, 2013 10:23 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
keeping it simple - right click on database - tasks - export data

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

Post #1446110
Posted Wednesday, April 24, 2013 12:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:15 PM
Points: 42, Visits: 50
thanks for the replies - that would definitely be nice and easy but unfortunately i dont have that option when I right click.
Post #1446185
Posted Wednesday, April 24, 2013 12:45 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 5,849, Visits: 12,585
is it there but greyed out?

what permissions do you have?


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

Post #1446187
Posted Wednesday, April 24, 2013 12:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:15 PM
Points: 42, Visits: 50
no its just not there. I think I have the free version of the management studio.
Post #1446189
Posted Wednesday, April 24, 2013 1:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1446205
Posted Monday, April 29, 2013 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:15 PM
Points: 42, Visits: 50
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?
Post #1447498
Posted Monday, April 29, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1447502
Posted Monday, April 29, 2013 7:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 13, 2013 12:15 PM
Points: 42, Visits: 50
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.
Post #1447532
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse