April 24, 2013 at 10:04 am
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?
April 24, 2013 at 10:22 am
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
April 24, 2013 at 10:23 am
keeping it simple - right click on database - tasks - export data
---------------------------------------------------------------------
April 24, 2013 at 12:42 pm
thanks for the replies - that would definitely be nice and easy but unfortunately i dont have that option when I right click.
April 24, 2013 at 12:45 pm
is it there but greyed out?
what permissions do you have?
---------------------------------------------------------------------
April 24, 2013 at 12:56 pm
no its just not there. I think I have the free version of the management studio.
April 24, 2013 at 1:23 pm
there is a very handy free plug in for SSMS here:
one of the options is to right click on a table in Object Explorer>>SSMS Tools>>Generate Insert Statements
Lowell
April 29, 2013 at 7:17 am
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?
April 29, 2013 at 7:21 am
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
April 29, 2013 at 7:50 am
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.
April 29, 2013 at 6:11 pm
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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply