467,134 Members | 924 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,134 developers. It's quick & easy.

copy data from one server to another

Hi all

is there a simple command where I can just copy some data from one
database server to another?
For example, lets say i have two servers that have the same table
called "States"
If I have all the state names in server1 but want to copy the all the
data to Server2, how would I write a command for this?

INSERT INTO [server2].[database2].states
SELECT statename FROM [server1].[database1].states

I have tried this but it gives me an error saying

"Invalid object name [server1].[database1].states"
"Invalid object name [server2].[database2].states"
Do I need a use [database] somewhere?

thanks
Harry

Nov 9 '08 #1
  • viewed: 21377
Share:
2 Replies
On Sun, 9 Nov 2008 12:42:40 -0800 (PST), Dev guy wrote:
>Hi all

is there a simple command where I can just copy some data from one
database server to another?
For example, lets say i have two servers that have the same table
called "States"
If I have all the state names in server1 but want to copy the all the
data to Server2, how would I write a command for this?

INSERT INTO [server2].[database2].states
SELECT statename FROM [server1].[database1].states

I have tried this but it gives me an error saying

"Invalid object name [server1].[database1].states"
"Invalid object name [server2].[database2].states"
Do I need a use [database] somewhere?
Hi Harry,

You forgot one part in the qualification of the object names: the
schema. If the tables are in the default schema, you can omit the schema
name but you still need the extra dot, otherwise SQL Server will take
[datebase1] to be the schema and [server1] to be the database.
INSERT INTO [server2].[database2]..states
SELECT statename FROM [server1].[database1]..states

The above (note the extra dots) should work.

However, depending on the amount of data, you might want to try if you
can't copy over the data faster using SSIS.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Nov 9 '08 #2
Dev guy (ho*************@gmail.com) writes:
is there a simple command where I can just copy some data from one
database server to another?
For example, lets say i have two servers that have the same table
called "States"
If I have all the state names in server1 but want to copy the all the
data to Server2, how would I write a command for this?

INSERT INTO [server2].[database2].states
SELECT statename FROM [server1].[database1].states

I have tried this but it gives me an error saying

"Invalid object name [server1].[database1].states"
"Invalid object name [server2].[database2].states"
Do I need a use [database] somewhere?
In addition to Hugo's post, you must also define the remote server
with sp_addlinkedserver. If this is another SQL Server, this may be
as easy as saying:

sp_addlinkedserver 'Server1'

Note that there is no need to specify the server name for the local
server. That assuming that you are on server2 and in database2,
this should be sufficient:

INSERT states (statecode, statename)
SELECT statecode, statename
FROM server1.database1.dbo.states
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 9 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Daniel Tan | last post: by
3 posts views Thread by colleen1980@gmail.com | last post: by
3 posts views Thread by waldek | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.