I am working on a project trying to consolidate two databases, DB1 and DB2, for my organization into a single database, DB3.

Initially our organization started with DB1. A few years back they made a new database called DB2 and moved on to using only DB2.

Currently, I am working on the Orders Table.

Since new rows were inserted into DB2 without importing existing rows from DB1, Order-id Column values started from 1 instead of continuing from where the Order-id Column in DB1 had ended.

I have many Order-id Column values that are the same in both Tables that represent different orders.

For example Order-id = 100 in DB1 refers to order "X", whereas Order-id = 100 in DB2 refers to order "Y".

If I insert rows from both DB1.Orders and DB2.Orders into DB3.Orders there will be duplicate rows representing different orders.

As I have not come across this sort of situation before, I need a suggested approach for dealing with this.

  • 3
    Are those order id values just in this table, or do they have references in other tables too? – Aaron Bertrand Aug 20 '14 at 19:25
  • Thank you all for your help: Max Vernon, Aaron Bertrand, Mike & MickyT. @Mike You have given a simple & an easy approach. Even though it has been answered by Mike. However, just to add for reference: Constraints are not applied in any database at the moment. – Irfan Salfi Aug 22 '14 at 12:28

Sometimes, the lack of an uint type in SQL Server can come in handy. If Order-id is defined as identity(1,1) in DB2, you likely do not have any negative Order-ids.

Consider importing all the legacy DB1..Orders.Order-id fields as the same number, but multiplied by -1. If there's a FK to Order-id in DB1, you would use the same multiple-by-negative-one conversion when those records are loaded into their corresponding tables in DB2.

With this method, you also have the benefit of easily identifying the legacy orders originally from DB1 because their Order-id is always negative (just be sure to document this.)

Edit - added an example

-- Assumptions:
-- DB1 and DB2 are on the same server
-- The table Orders is in the dbo schema
-- Order-id is an integer-type in DB1 and DB2
-- Order-id >= 0 in DB1 and DB2
-- Order-id is an identity(a, b), where a and b >=0 in DB2

-- Overview: Insert records from DB1 into DB2, but reverse the
-- sign of Order-id (i.e., positive to negative).

Use DB2

Set Identity_Insert dbo.Orders On;

Insert dbo.Orders
(Order-id, col2, col3)

Select (-1 * Order-id), col2, col3
From DB1.dbo.Orders;

Set Identity_Insert dbo.Orders Off;

-- Have related records? Insert them here.
  • 1
    If you have FK constraints with cascading updates, they will automatically apply the update to the referenced key. – MickyT Aug 20 '14 at 22:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.