How to copy or move data from one server table to another server table.

Question

3.00/5 (2 votes)

See more:

I am using two server. like server1 to server2.

i want to move or copy table data from server1 to server2 table.

i am using below query. but, it's not working.

select * into server1.dbo.dbname1.table1 from server1.dbo.dbname1.table1

Is there any other way to write the query..?

Posted 28-Mar-12 2:16am

gani7787

Add a Solution

Comments

Saral S Stalin 28-Mar-12 8:40am

What is the error you are getting? Have you added the second server as a linked server? Note that you need to enable distributed queries in both servers to move data in a single query. It is better to use SSIS packages if you are moving considerable amount of data and if the distributed queries are not enabled

3 solutions

Solution 3

You need to add the server as linked server. Then your query will work correctly.

Also note that there should not be more than three '.' separators in the servername.dbo.tablename.... in your query. Otherwise it will not work

Posted 28-Mar-12 4:29am

bbirajdar

Solution 1

1. Have separate connection objects for both servers.
2. fetch the data from one server and keep it in memory(datatable or dataset)
3. use second connection to write this data into.

Posted 28-Mar-12 2:20am

Rahul Rajat Singh

Comments

Sharma Richa 28-Mar-12 8:22am

can we do this by a single sql query

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Liju Sankar · Accepted Answer · 2012-03-28T04:24:00

You can also use import/export data task from SQL management studio.

Right click on your datbase -->Select Tasks and select import/export data based on your source or destination table.

How to: Run the SQL Server Import and Export Wizard
http://msdn.microsoft.com/en-us/library/ms140052.aspx

You can also use the Generate SQL Server Scripts Wizard[^] to help guide the creation of SQL script's that can do the following:

copy the table schema
any constraints (identity, default values, etc)
data within the table
and many other options if needed