August 23, 2010 at 5:27 am
We have one weird requirement :w00t: where we need to merge the data from two different dbs.
Both have same structure but with different values and we have to merge it.
Say in database DB1, table Product has values A, B, C with KeyID 1, 2, 3
and in DB2, Product table has values X, Y, Z with KeyID 1, 2, 3
The problem here is the row-identifiers table has a row-identifier(PK) and there are some dependencies on product table (OrderDetails)
We want to create one Product table with all the values from DB1 and DB2 database with UNIQUE ID
1,2,3,4,5,6 - A,B,C,X,Y,Z
How can we achieve this....;-)? Your suggestions are appreciated. Thanks in advance.
Abhijit - http://abhijitmore.wordpress.com
August 23, 2010 at 5:56 am
Abhijit
Surely you just create a table with an identity column, and then insert the values from your two tables into it? If this is too simplistic, then please describe what you require in more detail, preferably including table and key DDL for all the objects you mentioned.
John
August 23, 2010 at 6:38 am
Here is the structure information only for product table.
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](20) NULL,
[ProductStatusCode] [nchar](5) NOT NULL,
[CategoryCode] [nchar](5) NULL,
[ApplicationName] [nvarchar](50) NOT NULL,
[ChangeReason] [nvarchar](100) NULL,
[ModifiedBy] [nvarchar](20) NOT NULL,
[ModifiedDateTime] [datetime] NOT NULL,
[Rowguid] [uniqueidentifier] NOT NULL,
CONSTRAINT [Product_Pk] PRIMARY KEY CLUSTERED ([ProductID] ASC)
) ON [PRIMARY]
GO
DB1 & DB2 has Product table as mentioned above, with different values as i mentioned earlier. we want to merge both the table values in one table in some other database. the problem here is there are some dependencies on both product table in respective database. E.g. You can think of AdventureWorks database. Product, Order, OrderDetails relational example.
What we are expecting to do is migrating data from all DB's in one DB with respective data.
Abhijit - http://abhijitmore.wordpress.com
August 23, 2010 at 6:44 am
So please can we see DDL for the dependent tables, as well as for the foreign key constraints between them and the Product tables? Would you also be looking to copy the data from the dependent tables in DB1 and DB2 into the third database?
John
August 23, 2010 at 7:38 am
If you merge this stuff, one set of values, say those from DB1, will get to keep their identities. The other set, from DB2, will likely have to change. Which means you need to update your values in other tables.
Without more DDL, it's hard to determine how to do this. I've typically built a mapping table, with the old and new identity values and then use that to update the child tables.
August 23, 2010 at 8:34 am
as I mentioned you can think of Adventureworks database
. Customer
. Order
. Order Details
. Product
. Subcategory
- Product is defined by subcategory (FK in Product SubcategoryCode)
- Customers place the Orders
- Orders has order details
- N number of Order can have n number of Products
Abhijit - http://abhijitmore.wordpress.com
August 23, 2010 at 8:39 am
Given the choice of thinking of something and being able to see it, I choose the latter. I don't have AdventureWorks, in any case. If you want help, please provide the DDL or wait for someone with more patience to come along.
John
August 23, 2010 at 8:44 am
You are absolutely correct Steve, I agree with you. and this is the reason I am looking for some appraoch to handle this. One of the approach we found is twiking with script(as you mentioned). I am looking if there is any onther approach except SSIS and adding some fake column in DB3 which can identifiy the row from which database it is.
Abhijit - http://abhijitmore.wordpress.com
August 23, 2010 at 8:53 am
You could potentially build something, but it would be very cumbersome. The safest way is for you to track the old and new columns. I wouldn't do this in one of my existing tables, especially as it's usually a short term tracking, but I'd create a table(s) that would be something like
Create table ProductXfer
( oldProductID int
, newProductID int
)
If I had lots of tables, then I might do something like
Create Table TableMapping
( table varchar(100)
, oldID int
, newID int
)
August 23, 2010 at 9:08 am
Thanks Steve appreciate your views
Abhijit - http://abhijitmore.wordpress.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply