copy data from one server to another

[Dev guy]

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

[Hugo Kornelis]

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

[Erland Sommarskog]

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