Cross-server SQL
Solution 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
Solution 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
Solution 3
Are SQL Server Integration Services (SSIS) an option? If so, I'd use that.
Solution 4
Well I don't agree with your comment on replication. You can start a replication by creating a database from scratch, and you can control either the updates will be done by updating the available client database or simply recreating the database.
Automated replication will ease your work by automatically managing keys and relations.
I think the easiest thing to do is to start a snapshot replication through MSSQL Server Studio, get the T-SQL corresponding scripts (ie the corresponding T-SQL instructions for both publication and subscriptions), and record these scripts as part of a job in the Jobs list of the SQL Agent or as a replication job in the replications folder.
Solution 5
Would you be transferring the whole content of the database from one server to another or just some data from a couple of tables?
For both options SSIS would do the job especially if you are planning to to the transfer on a regular basis.
If you simply want to copy some data from 1 or 2 tables and prefer to do it using TSQL in SQL Management Studio then you can use linked server as suggested by pelser
- Set up the source database server as a linked server
- Use the following syntax to access data
select columnName1, columnName2, etc from serverName.databaseName.schemaName.tableName
Boris Callens
Senior .net programmer. Belgium(Antwerp) based. linked-in My real email is gmail.
Updated on September 22, 2020Comments
-
Boris Callens almost 4 years
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.
-
Galwegian almost 16 yearsAs you probably know, SSIS is the 'new' DTS for SQL Server 2005+
-
Boris Callens over 14 yearsA bit late, but I only now revisited this question and yes, this looks like an answer to my question. Although by now I realise my question should have been the question to the answer that I selected. All getting a bit complicated...
-
Boris Callens almost 11 yearsCool, didn't know this existed. On the other hand, technet.microsoft.com/en-us/library/ms179856.aspx says there have to be explicit regedits for that.
-
Magier almost 7 yearsThis does require the Linked Server as well, so basically this is just another kind of query following the same suggestion already made some years ago.
-
Fabian Pijcke about 5 yearsThe link is broken :-(
-
M H about 3 yearsyep, this is why people get flamed for link only answers
-
baltermia about 3 yearsWithout the link working this answer basically has the same information the question already gives.
-
Jack over 2 yearsdocs.microsoft.com/en-us/sql/relational-databases/… In SQL Server Management Studio (SSMS): Open Object Explorer. Expand Server Objects. Right-click Linked Servers. Select New Linked Server. You can also create a Synonym in your Primary Database to point to your Linked Server.