quote:

---

Originally posted by visakh16

are databases in same server? if yes you can use like

use db1
select * from table1
union all
select * from db2.dbo.table2

if they're in different server, you need to first setup a linked server and use like

use db1
select * from table1
union all
select * from server2.db2.dbo.table2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by Kristen

I would make a "translation table" that described that "Item_ID = 3,Item_Name = Consultancie" on DB2 should "map" onto "Item_ID = 1,Item_Name = Consultancy" on DB1.

It could just have two columns - DB1_Item_ID and DB2_Item_ID. You'll have to keep it up to date manually though ...

Or put a new column on the table in both databases and put a GUID in it - and keep that in Sync. so you can "join" on that.

---

SELECT T1.Item_ID, ...
FROM DB1.dbo.MyTable AS T1
UNION
SELECT [Item_ID]=X.DB1_Item_ID, ...
FROM DB2.dbo.MyTable AS T2
    JOIN
    (
        SELECT [DB1_Item_ID] = 1, [DB2_Item_ID] = 3
        UNION ALL SELECT 2, 6
        UNION ALL SELECT 3, 7
        ...
    ) AS X
        ON DB2_Item_ID = T2.Item_ID
WHERE ...

quote:

---

Originally posted by Kristen

Perhaps create a VIEW that presents the IDs in "sanitised" format. That could include an additional table JOIN'd 1:1 to get you the GUID that reliable synchronises the records from different databases. Or, if the number of rows is few, it could just use a CASE statement or a pseudo-table)

SELECT T1.Item_ID, ...
FROM DB1.dbo.MyTable AS T1
UNION
SELECT [Item_ID]=X.DB1_Item_ID, ...
FROM DB2.dbo.MyTable AS T2
    JOIN
    (
        SELECT [DB1_Item_ID] = 1, [DB2_Item_ID] = 3
        UNION ALL SELECT 2, 6
        UNION ALL SELECT 3, 7
        ...
    ) AS X
        ON DB2_Item_ID = T2.Item_ID
WHERE ...

then the maintenance of the "synchronisation" needs to take place within the script (or wrap that all in a VIEW if you need to use it from several places)

---