How to combine data from different columns from different databases from different vendors into one table?

Question

Hello,

Say i have a table named tbl1 in mysql :-

bookid int
name varchar(20)
price int
categoryid int

And then i have a 2nd table, tbl2 in Oracle :-

pubid int
name varchar(20)
addr varchar(50)

I want to combine these two into MS Sql Server 2008 named tbl3 whose structure should be :-

bookid int
name varchar(20)
price int
pubid int
name varchar(20)

Note that i know the tables are not meaningful but i just need the concept whether this thing is possible or not. And if yes then how? Which queries should i write? I don't want to use Sql Server Integration Services.

Thanks in advance :)

Answer 1

Do they have a common column that you can run a join on. For example does the categoryid in the first table match the pubid in the second. Does name from table1 match with name in table2. If so is name the key, no other book with have the same name with each other. If so run a join on the tables using name as the common key.

If there is no common key then you can do select * from table1, table2, ..., tablen. This would work into a really large table. If you join two tables with 12 rows each then your resulting table would have 12*12 = 144 rows in total.

Answer 2

You will need something that can communicate with each of the databases. If SQL Server is the desination, and you don't want to use DTS/SSIS, then you could use Linked Servers. The only question is how the data from Oracle relates to the data from MySQL since there is no matching column. Regardless, persuming you had a Linked Server called "ORACLESERVER" and another called "MYSQLSEVER", you could do something like (assuming that name was the linking column):

Insert SqlServerDbName.SchemaName.TableName(....)
Select ...
From ORACLESERVER.DbName.SchemaName.tbl1 As T1
    Join MYSQLSERVER.DbName.SchemaName.tbl2 As T2
        On T2.name = T1.name

Another solution would be to use OPENROWSET to achieve a similar result.