Angus
asked on
Copy between MS SQL Instances using SQL
Folks,
I have two databases on seperate instances.
I would like to using INSERT SELECT statement across the instances of MS SQL.
At the moment I am using Java and the algorithm is to do a SELECT on one connection and an INSERT on another.
However I am thinking that there must be a way of doing this using a INSERT SELECT
Any ideas?
Cheers
Angus
I have two databases on seperate instances.
I would like to using INSERT SELECT statement across the instances of MS SQL.
At the moment I am using Java and the algorithm is to do a SELECT on one connection and an INSERT on another.
However I am thinking that there must be a way of doing this using a INSERT SELECT
Any ideas?
Cheers
Angus
INSERT INTO Db1.dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Db2.dbo.Table
SELECT YourColumnsList...
FROM Db2.dbo.Table
If the two databases are on different servers, you can create a linked server on one of the servers and do the same, with a small difference:
INSERT INTO Server1.Db1.dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Server2.Db2.dbo.Table
INSERT INTO Server1.Db1.dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Server2.Db2.dbo.Table
Or you could just let DTS handle it :-)
ASKER
Thanks for your extremely prompt answer - it has been very helpful
One further question.
I have managed to setup a Linked Server (using the setting 'be made using this security context') - however the name of the server is 192.168.100.200\Production
On that basis, how do I reference the server in the SQL Statement
Cheers
Angus
One further question.
I have managed to setup a Linked Server (using the setting 'be made using this security context') - however the name of the server is 192.168.100.200\Production
On that basis, how do I reference the server in the SQL Statement
Cheers
Angus
ASKER
Sorry.. to be more specific
INSERT INTO Server1.Db1.dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Server2.Db2.dbo.Table
thus replacing Server2 with '192.168.100.200/Productio n' oddly enough gives an error... any way of creating an alias
Cheers
Angus
INSERT INTO Server1.Db1.dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Server2.Db2.dbo.Table
thus replacing Server2 with '192.168.100.200/Productio
Cheers
Angus
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BIG BIG THANKS!!!
p.s - you broke the speed record for the quickest answer...!!!
p.s - you broke the speed record for the quickest answer...!!!
INSERT INTO Db1Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Db2Table