Merging two related tables from 2 databses

Question

I have 2 tables "Order and "OrderDetails" with the similar structure below.Order.id is foreign key for OrderDetails

The same structure exists in 3 different regions Databases.

Now

we have a requirement to merge 3 regions tables to one . ie (from 2*3=6 tables to 2 tables)

Order.id can be duplicated in all regions

eg:

region1DB.Order.id{1,2,3,4,5} , region2DB.order.id{1,2,3,4,5}

Table structure

I need a final output of 2 tables with data from 3 databases, with all the relationships.

eg: if first region table has 10 orderid's , while merging the data from second region order.id will be starting/updated with 11,12,13(wherein the actual number was 1,2,3,4 in second region) and in Orderdeatils it should be reflected as well.

Could you help me with a query for this merge? or any other latest technique available like sql merge? If yes please give me a sample.

I am new to sql and I couldn't see a perfect example merging relationship tables.

Here is my query to join the first table

select  tab1.ID, tab1.OrderName
from [DB1].[dbo].[Order] tab1
union all
select tab2.id + (select max(id) from [DB1].[dbo].[Order]) ,tab2.OrderName
from [DB2].[dbo].[Order] tab2

-> query for merging second table(OrderDetails)

declare @t varchar(8000)

set @t=(select max(id) from [DB1].[dbo].[Order]) from [DB1].[dbo].[OrderDetails] tab1

select tab1.OrderID, tab1.productCode from [DB1].[dbo].[OrderDetails] tab1

union all

select

case when

tab2.OrderID> (@t) then tab2.OrderID + (@t)

when

tab2.OrderID<=(@t) then tab2.OrderID

END

,tab2.productCode

from [DB2].[dbo].[OrderDetails] tab2

order by OrderID

Thanks

SNA

Answer 1

I'm assuming you are using SQL Server 2008 and are able to actually use the MERGE command.

I've used the approach outlined at this link before combine the MERGE and OUTPUT statements. It is useful for performing set based operations such as getting data from one source into a destination and then retaining the inserted identity values for use when linking up child related tables from the source.

The example at the abovementioned link that is of use is as follows;

DECLARE @source TABLE (

   [id] INT PRIMARY KEY

,  [name] VARCHAR(10)

);

INSERT @source VALUES(1000,'Harold'),(2000,'Madge');

DECLARE @destination TABLE (

   [id] INT PRIMARY KEY IDENTITY(1,1)

,  NAME VARCHAR(10)

);



MERGE  @destination

USING  (SELECT [id], [name] FROM @source) AS [source]

ON     (1=0) --arbitrary join condition

WHEN   NOT MATCHED THEN

       INSERT (name)

       VALUES  (source.Name)

       OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name; 

You can see when you run this example, you end up with 1000, 2000 as Ids from @Source, and then the new identity values of 1 and 2 in your @Destination alongside the orginal values from @Source.

You can then combine this with OUTPUT INTO to get your OldId and NewId side by side to essentially re-map the relationships in your new data structure. I can't delve into this now as I am short on time, but you can find examples of the SQL Server OUTPUT statement on SO answers like this or the MSDN Output page (see the example sections) or various other sites.

You can even test this against your data by simply inserting into table variables or temp tables as the author has done in the links above to test that it'll work for you.

Best of luck.