Cross-server SQL

Question

I want to port data from one server's database to another server's database. The databases are both on a different mssql 2005 server. Replication is probably not an option since the destination database is generated from scratch on a [time interval] basis.

Preferebly I would do something like

insert * from db1/table1 into db2/table2 where rule1 = true 

It's obvious that connection credentials would go in somehwere in this script.

Answer 1

I think what you want to do is create a linked server as per this msdn article. You would then select using a 4 part object name eg:

Select * From ServerName.DbName.SchemaName.TableName 

Answer 2

You can use Open Data Source Like this :

EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO  EXEC sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO   SELECT  * FROM    OPENDATASOURCE('SQLOLEDB',                    'Data Source=<Ip Of Your Server>;                     User ID=<SQL User Name>;Password=<SQL password>').<DataBase name>.<SchemaName>.<Table Or View Name>  Go