SELECT INTO or CREATE TABLE

[bxlorenz]

I have a SQL 2005 database and a SQL 2008 database. I want a simple SQL statement that will create/copy a table and its contents from 2005 into 2008 that I can use in a C# program. I know I can export using Mgmt Studio but I want to do in a a program. I am able to perform a SELECT INTO [MyDatabase2].[dbo].[mytable] SELECT * FROM [Mydatabase1].[dbo].[mytable] when they exist in different databases in the same machine and only in the same Database Instance but how do I across multiple instances or multiple machines. Can anyone help ? Thanks

[Blikkies]

Use this to connect to other server

EXEC sp_addlinkedsrvlogin 'server', 'false', 'userLocal', 'userRemote', 'remotePassword'

Code should look like this:

SELECT INTO [server].[MyDatabase2].[dbo].[mytable] FROM [Mydatabase1].[dbo].[mytable]

modified on Friday, September 25, 2009 2:06 AM

[bxlorenz]

Blikkies, Thanks for the info. Just a couple follow up questions. So on the 2005 SQLExpress Server I have an instance of SQLEXPRESS and on 2008 SQL I have an DB instance of MY2008DB I would do the following if I am running on the 2008 system. EXEC sp_addlinkedsrvlogin '\\MY2005Server\SQLEXPRESS', 'false', 'sa', 'sa', 'sa' SELECT INTO [MyDatabase2].[dbo].[mytable] FROM [\\MY2005Server\SQLEXPRESS].[Mydatabase1].[dbo].[mytable] and if I was running this on the 2005 server it would be the opposite ? EXEC sp_addlinkedsrvlogin '\\MY2008Server\MY2008DB', 'false', 'sa', 'sa', 'sa' SELECT INTO [\\MY2008Server\MY2008DB].[MyDatabase2].[dbo].[mytable] FROM [Mydatabase1].[dbo].[mytable] Or how does that work with the DB Instance name ? thanks