Question on copying over overlapping data from one database to another...

... and preserving the relationships.
(Note, this is more of a SQL question than a SQL-related ASP.NET question...)
Say I have two databases, D1 and D2, with the same three tables:
Companies
Departments
Employees
With the standard one-to-many relationships down the line, with each
table having a PK, IDENTITY field like CompanyID, DepartmentID, and
EmployeeID.

I have a smattering of data in each of D1 and D2 for these tables with
overlaps in the ID field values. What I want to be able to do is copy
over D1's data to D2, preserving the relationships in D1 even though
there are ID overlaps in D2. So the tool I'd use would have to be
smart enough to check for ID dups in D2 and appropriate change the ID
values in D1's tables, maintaining the relationships.
Is there some built-in SQL tool to do this or do I have to do this myself?
Thanks!

There may be 3rd Party tools that do this but there's not any way, I'm aware of, of doing it in Enterprise Manager or Management Server Express. I do have an idea, though, of a way you could at least merge the tables using standard SQL:

For instance, if we take the Northwind database and imagine you have two Products tables in it, both with the exact same columns and both populated with data where the ProductID (PK) might be the same in both tables. We shall call these two tablesProducts andProducts2. Now, if you wanted to merge these two tables into one new table with a new, unqiue primary key you would do this:

Create a new table called (say) Products_Temp with the same structure as Products and make sure that ProductID is set as an Identity column. The easiest way is to create a script from Products table which would look like:

CREATETABLE [dbo].[Products](

[ProductID] [int]IDENTITY(1,1)NOTNULL,

[ProductName] [nvarchar](40)COLLATE Latin1_General_CI_ASNOTNULL,

[SupplierID] [int]NULL,

[CategoryID] [int]NULL,

[QuantityPerUnit] [nvarchar](20)COLLATE Latin1_General_CI_ASNULL,

[UnitPrice] [money]NULLCONSTRAINT [DF_Products_UnitPrice]DEFAULT((0)),

[UnitsInStock] [smallint]NULLCONSTRAINT [DF_Products_UnitsInStock]DEFAULT((0)),

[UnitsOnOrder] [smallint]NULLCONSTRAINT [DF_Products_UnitsOnOrder]DEFAULT((0)),

[ReorderLevel] [smallint]NULLCONSTRAINT [DF_Products_ReorderLevel]DEFAULT((0)),

[Discontinued] [bit]NOTNULLCONSTRAINT [DF_Products_Discontinued]DEFAULT((0)),

CONSTRAINT [PK_Products]PRIMARYKEYCLUSTERED

(

[ProductID]ASC

)WITH(IGNORE_DUP_KEY=OFF)ON [PRIMARY]

)ON [PRIMARY]

Then to merge the two tables you would UNION them together on all columnsexceptthe primary key column (ProductID) and place the results into the Products_Temp table you just created. Eg.

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

INTO Product_TEMP

FROM

(SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products

UNION

SELECT ProductName, SupplierID, CategoryID, QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued

FROM Products2)

AS Temp;

Now Products_Temp will contain the merged data from both tables (with any exact duplicate rows removed) and each row will now have a unique primary key (because of Identity insert). I know this isn't that much help because it doesn't preserve the PK > FK relationships between multiple tables, but it might give you some ideas or inspiration... Good luck :)

|||Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.
|||Connect, I was not aware of any built-in functionality in SQL Server 2000, and figured it would have to be scripted.
Does anyone know of a third-party tool that accomplishes this?

Blake05 wrote:

Scott,
If you have sql enterprise you can use there management studio and do this with a breeze. even if the database is local or remote. You can execute scripts on the databases, copy, backup, merge. basically everyhing. I hope this helps.

Blake05, how can I use EM to copy data from one DB to another when there are overlapping ID values between the two databases' tables?
Thanks
|||Yes you can
|||You don't need third party tool create Unique index with IGNORE_DUP_KEY option and do standard INSERT INTO and in SQL Server 2000 and below that statement will not affect update statements. What happens is SQL Server will not insert the duplicate values it will insert only the unique values. I am assuming this is what you want and run a search for the above in SQL Server BOL (books online). Hope this helps.