August 12, 2008 at 8:53 am
how to get product inventory from three different databases each have product table in it to the fourth new database (i.e centralized database)
Thanks
August 12, 2008 at 10:49 am
if databases are in the same server
===========================
select * from mydb1.dbo.product where product_id =99999
union
select * from mydb2.dbo.product where product_id =99999
union
select * from mydb3.dbo.product where product_id =99999
if the database are in different servers
=============================
Create a distributed query by using the Openrowset function and passing the connection parameters that define your sql server. The provider is SQLOLEDB
example:
SELECT t1.*
FROM OPENROWSET('SQLOLEDB','server1';'sa';'MyPass',
'SELECT * FROM mydb1.dbo.product where product_id =99999') as t1
union
SELECT t2.*
FROM OPENROWSET('SQLOLEDB','server2';'sa';'MyPass',
'SELECT * FROM mydb2.dbo.product where product_id =99999') as t2
union
SELECT t3.*
FROM OPENROWSET('SQLOLEDB','server3';'sa';'MyPass',
'SELECT * FROM mydb3.dbo.product where product_id =99999') as t3
August 12, 2008 at 11:06 am
Thanks
but some productid's are different for same product in each database
August 12, 2008 at 11:55 am
you have two solutions
1) you have to create a table alternative_products
with product_id , alt_prod_id , and fill that table with with alternative codes and use it in queries
2) you have to unify product_id , add new field in every table called standard_code , fill it, and use it in your search
The filling of data/ standard codes is not the responsibility of DBA , but user responsibility. Discuss that problem with users'manager and let him suggest the solution.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply