locked
insert values into identity column RRS feed

  • Question

  • Let me explain what is happening

    I created a table that used an auto incrementing identity column and was also used as the primary key (green design).  I'm creating a new table that has the same type of column that is only used to relate tables.  I have another column that I would like to use the auto-increment identity for this column but I also will need to be able to insert my own values.  What I would like is to not interfear with the auto numbering when I insert my own values.

    Can I do this or will the auto-increment continue from the highest value that I inserted.  Before everyone asks why I want to do this, the reason is I intend to import the data from the old design to the new design and would like to keep the original numbers.


    Always Learning new Things
    Thursday, December 30, 2010 6:44 PM

Answers

All replies

  • You need to run

    SET IDENTITY_INSERT ON 

    for that table in order to be able to insert values into the identity column.

    See BOL Reference


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by Nightman28 Thursday, December 30, 2010 7:42 PM
    Thursday, December 30, 2010 7:00 PM
    Answerer
  • If you use IDENTITY_INSERT , if the value you insert is higher that the current identity value, it will use the new value. You can also use DBCC CHECKIDENT to change the identity value to whatever you want.

    Ken Simmons
    http://cybersql.blogspot.com
    http://twitter.com/kensimmons
    • Marked as answer by Nightman28 Thursday, December 30, 2010 7:42 PM
    Thursday, December 30, 2010 7:01 PM
  • So If I use indentity_insert and say I have it seeded to start at 100, but I insert a value of 3000 it will then continue from 3000 and not go back and fill in between 100 and 3000.  Is that correct in my understanding?  I would prefer it not to happen that way.

    Can use dbcc checkident to insert a large block of data? Second I'm thinking that possibly for some unknown reason of possible future things I may allow the user themselves to insert a value as long as its not used.

    I do appologize for my nuubie questions, as I have learned better database designing but still green with the actual implementation of those designs.


    Always Learning new Things
    Thursday, December 30, 2010 7:16 PM
  • You're correct in your understanding of what is going to happen. If you use DBCC checkindent after you inserted a large block of data and re-set it back to small number, then it will fill the gap - you just need to be careful and re-run DBCC checkindent back when the gap will be filled. 
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, December 30, 2010 7:23 PM
    Answerer
  • You are correct in that the new identity will continue from 3000. DBCC CHECKIDENT will just reset the identity value to whatever you want. There is no good way to go back and fill in gaps and you will probably have issues letting users enter identity values. Here is some code that shows some examples that may help you understand what each command does.

     

    CREATE TABLE Test(i INT IDENTITY, i1 INT)
    
    INSERT INTO Test VALUES (1)
    
    SET IDENTITY_INSERT TEST ON
    
    INSERT INTO Test (i, i1) VALUES (500,2)
    
    SET IDENTITY_INSERT Test OFF
    
    INSERT INTO Test VALUES (3)
    
    SELECT * FROM Test
    
    DBCC CHECKIDENT (Test, RESEED, 1)
    
    INSERT INTO Test VALUES (4)
    
    SELECT * FROM Test
    


    Ken Simmons
    http://cybersql.blogspot.com
    http://twitter.com/kensimmons
    Thursday, December 30, 2010 7:26 PM
  • Thank you to both of you.  After consideration I have decided to allow the new design to renumber the data from the old tables.
    Always Learning new Things
    Thursday, December 30, 2010 7:46 PM