Copy table+values from one server to another

Question

Post reply

Copy table+values from one server to another

a_ud

SSCrazy

Points: 2457
More actions
May 15, 2013 at 7:12 am

#277540

This might be the dumbest question ever, but I really don't know/remember how this is done.
Let's say you've got 2 servers, S1 and S2. In S1 you've got the source table with the values you want to transfer to a target table in S2.
Assuming S2 already has the table created (easily done by Right click>Script table as...>CREATE), how do you copy one table to another?
I thought this was as easy as using the Script table as...>INSERT, but that doesn't work: it throws an error, and when you are on a different server the values don't display after the VALUES reserved word.
I know I can import the data, but what I'd like is to copy the data using SQL and referencing server S1.
Any suggestions on how to do this? Thanks,

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

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

UconnDBA Default port Points: 1439 More actions · Answer 1

Can use Export. Right click on the source database, then Tasks>Export Data...

a_ud SSCrazy Points: 2457 More actions · Answer 2

Thanks, but as I said, I thought there could be another method (ideally, SQL referencing the server name).

Is this possible?

UconnDBA Default port Points: 1439 More actions · Answer 3

Sorry. Missed your comment about Import. Have not copied objects between servers using SQL script. Normally use SSIS. What your looking to do could possibly be done if they are Linked Servers, but I have avoided using Linked servers.

subhajeetsur SSC-Addicted Points: 453 More actions · Answer 4

You can link the two servers as linked servers. Then tables can be accessed as per your requirement. It is better to export the table as a file from one server using bcp out and load that using bcp in in target database server.