Copying data from one server to another -- sql server new users

dew
1/17/2006 4:53:47 PM

Is there a way to programmtically copy data from a database on one server to
another database on another server without them being linked? I can do it
through dts but not through t-sql. For instance,

CREATE TABLE [dbo].[tblStuff] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]
GO

Insert into tblwmStuff (Code, Description)
Select Code, Desc from Server2.Database2.dbo.tblOldStuff

I get a message saying it could not find Server2, use sp_addlinkedServer

I really don't want to link the 2 servers.

David Portas
1/18/2006 12:30:22 AM

[quoted text, click to view]

What do you have against linking servers? It's easy enough to drop a linked
server after you've finished with it.

DTS is the other main alternative. Or you could use BCP to export and then
load the data.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

sql server new users : Copying data from one server to another