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

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
onesegunAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
both on the same server?

insert into dbname.schema.tablename(c1,c2,c3)
select c1,c2,c3 from dbname2.schema.tablename2
0
 
onesegunAuthor Commented:
No both on different servers.

Is it still possible?

Thanks,

OS
0
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
All Courses

From novice to tech pro — start learning today.