Solved

SQL Server: Select query from one database and insert into a table in another database

Posted on 2014-01-30
4
12,341 Views
Last Modified: 2014-02-17
Hi All,

I was wondering if it was at all possible in SQL Server to query one database and insert the results into a table in another database in one go?

Thanks,

OS
0
Comment
Question by:onesegun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39820571
both on the same server?

insert into dbname.schema.tablename(c1,c2,c3)
select c1,c2,c3 from dbname2.schema.tablename2
0
 

Author Comment

by:onesegun
ID: 39820698
No both on different servers.

Is it still possible?

Thanks,

OS
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 50 total points
ID: 39820720
yes you can but you will have to use OpenRowset for your Select query. Check http://technet.microsoft.com/en-us/library/ms190312.aspx
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 50 total points
ID: 39821029
Solution 1

if you can establish a linked server connection on Server A to Server B
Then you can use the below code to do that

INSERT INTO <your Table>
SELECT * FROM [Server B].<Data base on server B>.<schema>.<Table on Server B>

Open in new window


Establishing a linked server is an easy process and a one time job...
You can follow the below tutorial to do the same

http://msdn.microsoft.com/en-us/library/aa560998.aspx
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 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