Click here to Skip to main content
15,844,477 members
Please Sign up or sign in to vote.
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
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

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
 
Share this answer
 
v2
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
 
Share this answer
 
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.
 
Share this answer
 
Comments
Sharma Richa 28-Mar-12 8:22am    
can we do this by a single sql query

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900