Removing Identity Specification definitions via TSQL and then readding?

  • Hello all. I mentioned in a post from a couple of days ago that I was in the process of loading data into dozens of tables in a DEV database from a PROD database. Essentially just getting fresh data. Dropping the DEV database and restoring it from a fresh copy of the PROD database is not an option. Therefore, I've been working on a Stored Proc that does all this magic. For the most part, it mostly works great.

    I am running into one problem and my solution is a hack. Many tables have the PK as a AutoNumber. It simply increments by 1 (Identity). Through out the life of the table, records could be deleted (not my design). So the PKs in the Table will have numeric gaps. For example:

    SOURCE TBL

    IDDescription

    1Thing 1

    2Thing 2

    5Thing 5

    6Thing 6

    7Thing 7

    8Thing 8

    9Thing 9

    10Thing 10

    Notice that records 3&4 were deleted at some point in time. If I try to insert these into a mirror table/database, it then looks like this:

    DESTINATION TBL

    IDDescription

    1Thing 1

    2Thing 2

    3Thing 5

    4Thing 6

    5Thing 7

    6Thing 8

    7Thing 9

    8Thing 10

    Ruh Roh Scooby. They don't match. Surely this is a common scenario. My hack is to change the design manually of the DESTINATION Table(s) and remove that IDENTITY setting. Just have the IDs fields as ints & PKs. Once the inserting is all over, manually change them back.

    Perhaps there is better solution but my ideal solution was to, in my Stored Proc, remove the IDENTITY setting/definitions in the DESTINATION TBL, do the insert, and the add the IDENTITY settings back when finished. Make sense?

    So in my TSQL, change this:

    [ID] [int] IDENTITY(1,1) NOT NULL,

    to this:

    [ID] [int] NOT NULL

    ...and then do my Insert. When finished, change it back.

    Make sense? I was hoping someone could provide a sample of code that does this.

    Thanks for the input!

  • Instead of removing the identity specs, take a look at: SET IDENTITY_INSERT tablename OFF



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • You could use "SET IDENTITY_INSERT <table_name> ON" to insert identity values into a table. Just remember to switch it off afterwards.

    😎

  • Thanks! Will give it a shot.

  • ...almost there. Almost. Any idea as to why I can only do this once in my Stored Prod?

    For example:

    SET IDENTITY_INSERT pnd_Product ON

    SET IDENTITY_INSERT pnd_Customer ON

    Msg 8107, Level 16, State 1, Procedure sp_TDL_RefreshTaradelStgTables, Line 164

    IDENTITY_INSERT is already ON for table 'xxxx.dbo.pnd_Product'. Cannot perform SET operation for table 'pnd_Customer'.

    ????

    Checked many references. Can't see anything wrong..but most online examples only so one example. I need to do this for about 6 tables.

  • Nevermind. Found answer:

    "At any time, only one table in a session can have the IDENTITY_INSERT property set to ON..."

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply