Link to home
Start Free TrialLog in
Avatar of Angus
AngusFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of rafrancisco
rafrancisco

If the two databases are in the same server, you can do INSERT...SELECT like this:

INSERT INTO Db1Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Db2Table
INSERT INTO Db1.dbo.Table (YourColumnsList...)
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
Or you could just let DTS handle it :-)
Avatar of Angus

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
Avatar of 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/Production' oddly enough gives an error... any way of creating an alias

Cheers
Angus
ASKER CERTIFIED SOLUTION
Avatar of rafrancisco
rafrancisco

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Angus

ASKER

BIG BIG THANKS!!!

p.s  - you broke the speed record for the quickest answer...!!!