[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Copy between MS SQL Instances using SQL

Posted on 2005-05-04
8
Medium Priority
?
286 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:amacfarl
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

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

INSERT INTO Db1Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Db2Table
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13930981
INSERT INTO Db1.dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM Db2.dbo.Table
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13930997
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13931128
Or you could just let DTS handle it :-)
0
 
LVL 2

Author Comment

by:amacfarl
ID: 13931248
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
0
 
LVL 2

Author Comment

by:amacfarl
ID: 13931372
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
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13931385
To reference the server in the SQL statement will be like this:

INSERT INTO dbo.Table (YourColumnsList...)
SELECT YourColumnsList...
FROM [192.168.100.200\Production].Db2.dbo.Table

You have to put it in square brackets.
0
 
LVL 2

Author Comment

by:amacfarl
ID: 13931428
BIG BIG THANKS!!!

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question