locked
How to migrate identities from one database to another RRS feed

  • Question

  • I have several  tables in one database, e.g., tablea, tableb. These tables conatin a a column id make use of identity(1,1).

    IN addition I have another table:  BridgeTableaTableb  which has two columns tablea_id, tableb_id which contains id of tablea and tableb.

    I am On SQL Server 2008; so I can use generate scripts from SSIS to create schema and data all in one file.

    Now I want to move the 3 tables  to another database. If just run the script created by sql server in new database; will it work in particular the brige table which has ids from old database.

    Thursday, May 9, 2013 2:18 PM

Answers

  • Let's define our terms:

    First, let's name the old database dbOld, and your tables as

    1. dbOld.dbo.TableA,
    2. dbOld, dbo.TableB, and
    3. dbOld.dbo.dbBridgeTableATableB.

    What you're trying to accomplish is to create an identical schema in a new database (dbNew), with identical values.

    In a perfect world, all you'd have to do is script the DDL for the new tables, and script INSERT statements (or SELECT INTO). Since you're inserting records into dbNew.dbo.TableA in the same order that they appear in dbOld.dbo.TableA, you should end up with the same values, right?

    Not necessarily.

    If any rows in either dbOld.dbo.TableA or dbOld.dbo.TableB have been deleted, you'll have gaps in the sequence of numbers in those tables. When you insert records based on those tables into dbNew, you'll probably have Foreign Key violation errors when you insert rows into dbNew.dbo.BridgeTableATableB.

    Or--infinitely worse--you might NOT have errors. In which case you will have introduced hidden errors into your new database, with potentially disastrous consequences.

    Let me strongly encourage you to NOT just assume that you can auto-generate the script for these tables, unless you are absolutely, positively sure that each of the values in the identity columns is sequential--that is, there are no gaps.

    A wiser course of action is to use the IDENTITY_INSERT statement. That permits you to insert values into the identity column--enabling you to script this with no trouble at all. Here's how:

    1. Script your DDL code for each of the three tables. Include your primary key constraints, foreign-key constraints, etc.
    2. Script dbOld.dbo.TableA, using SET INDENTITY_INSERT dbNew.dbo.TableA ON; -- be sure to include the integer value for your identity column in your INSERT statements.
    3. At the end of your script block, add SET IDENTITY_INSERT dbNew.dbo.TableA OFF;
    4. Do the same thing with dbNew.dbo.TableB,
    5. And script the INSERTs to dbNew.dbo.BridgeTableATableB (without using IDENTITY_INSERT, since there aren't identity columns).

    That approach will work whether or not your identity column values are contiguous or not. It is, IMHO, a far safer approach to take to the problem.



    • Proposed as answer by JohnMurdoch Thursday, May 9, 2013 3:26 PM
    • Edited by JohnMurdoch Thursday, May 9, 2013 3:32 PM
    • Marked as answer by Prem Mehrotra Thursday, May 9, 2013 4:25 PM
    Thursday, May 9, 2013 3:25 PM

All replies

  • if you bring the script along with data and execute the same in another database, It will create all three tables in that database. It has to work the same way it worked in old DB

    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Thursday, May 9, 2013 2:33 PM
  • Is that possible to Issue SELECT * INTO command on the destination, as you will preserve all the data, make sure you create the keys after the script is completed.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, May 9, 2013 2:33 PM
    Answerer
  • You should be able to recreate your tables in your target database if your script includes all your table DDL and data inserts.
    Thursday, May 9, 2013 2:59 PM
  • Let's define our terms:

    First, let's name the old database dbOld, and your tables as

    1. dbOld.dbo.TableA,
    2. dbOld, dbo.TableB, and
    3. dbOld.dbo.dbBridgeTableATableB.

    What you're trying to accomplish is to create an identical schema in a new database (dbNew), with identical values.

    In a perfect world, all you'd have to do is script the DDL for the new tables, and script INSERT statements (or SELECT INTO). Since you're inserting records into dbNew.dbo.TableA in the same order that they appear in dbOld.dbo.TableA, you should end up with the same values, right?

    Not necessarily.

    If any rows in either dbOld.dbo.TableA or dbOld.dbo.TableB have been deleted, you'll have gaps in the sequence of numbers in those tables. When you insert records based on those tables into dbNew, you'll probably have Foreign Key violation errors when you insert rows into dbNew.dbo.BridgeTableATableB.

    Or--infinitely worse--you might NOT have errors. In which case you will have introduced hidden errors into your new database, with potentially disastrous consequences.

    Let me strongly encourage you to NOT just assume that you can auto-generate the script for these tables, unless you are absolutely, positively sure that each of the values in the identity columns is sequential--that is, there are no gaps.

    A wiser course of action is to use the IDENTITY_INSERT statement. That permits you to insert values into the identity column--enabling you to script this with no trouble at all. Here's how:

    1. Script your DDL code for each of the three tables. Include your primary key constraints, foreign-key constraints, etc.
    2. Script dbOld.dbo.TableA, using SET INDENTITY_INSERT dbNew.dbo.TableA ON; -- be sure to include the integer value for your identity column in your INSERT statements.
    3. At the end of your script block, add SET IDENTITY_INSERT dbNew.dbo.TableA OFF;
    4. Do the same thing with dbNew.dbo.TableB,
    5. And script the INSERTs to dbNew.dbo.BridgeTableATableB (without using IDENTITY_INSERT, since there aren't identity columns).

    That approach will work whether or not your identity column values are contiguous or not. It is, IMHO, a far safer approach to take to the problem.



    • Proposed as answer by JohnMurdoch Thursday, May 9, 2013 3:26 PM
    • Edited by JohnMurdoch Thursday, May 9, 2013 3:32 PM
    • Marked as answer by Prem Mehrotra Thursday, May 9, 2013 4:25 PM
    Thursday, May 9, 2013 3:25 PM
  • Dinesh,

    That's true if (and only if) none of the rows in TableA or TableB have ever been deleted. If there are gaps in the sequence of integers in either identity column, the INSERT statements to the new BridgeTableATableB table will likely blow up with foreign-key errors. Or--worse--will not blow up, but will introduce incorrect relations between unrelated values.

    Thursday, May 9, 2013 3:31 PM
  • John Mudoch:

    Thanks a lot for provding a clear detailed explanation. These forums are excellent because people like you are willing to help.

    Prem

    Thanks to others as well,

    Prem

    Thursday, May 9, 2013 4:27 PM
  • Dinesh,

    That's true if (and only if) none of the rows in TableA or TableB have ever been deleted. If there are gaps in the sequence of integers in either identity column, the INSERT statements to the new BridgeTableATableB table will likely blow up with foreign-key errors. Or--worse--will not blow up, but will introduce incorrect relations between unrelated values.

    John, thanks for the info :)


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Thursday, May 9, 2013 4:37 PM
  • What if the new database has some records already inserted. How would you manage Identity value conflicts ?
    Monday, December 16, 2013 7:10 AM