CODEWITHSUNDEEP
sqlsql-server
user2018408
user2018408

Reputation: 79

Inserting data from different dbs table to another db in the same server

I have table 1 in the db1 and table 2 in the db2. I want to insert the table1 and table2 data into table3 which is in the db3 structure of all three tables is same and all dbs on same server.

Can any one help how to do that using insert statement?

Upvotes: 0

Views: 180

Answers (2)

AnandPhadke
AnandPhadke

Reputation: 13496

Use db3
Go

INSERT INTO Table3
select * from 
(
select * from db1..Table1
union all
select * from db2..Table2
) a

Upvotes: 0

G. Stoynev
G. Stoynev

Reputation: 7783

Assuming MS SQL Server and db1, db2 and db3 "structures" are different databases:

USE db3
INSERT INTO table3
SELECT  CombinedTables.[Alias1], CombinedTables.[Alias2]
(
    SELECT col1 AS [Alias1], col2 AS [Alias2] FROM db1.[schema, <dbo>].table1
    UNION
    SELECT col1, col2 FROM db2.[schema, <dbo>].table2
) AS CombinedTables

Upvotes: 1

Related Questions