Adding a row keyed by an IDENTITY

Posted on 2009-12-29
Last Modified: 2012-05-08
I have an integer key that is an IDENTITY starting at 100.  Can I INSERT a row and specify the identity key value if it's less than 100?  Can I ever specify the IDENTITY value in an INSERT?

Question by:hefterr
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    yes, you can, using the SET IDENTITY_INSERT syntax:
    LVL 16

    Expert Comment

    never tried it, but looks like you can with IDENTITY_INSERT
    LVL 60

    Accepted Solution

    set identity_insert tablename on

    insert into tablename(field1, field2, idfield)
    select 'val', 'val', 3

    set identity_insert off

    --VERY important to set it back to off
    LVL 1

    Author Comment

    Hey Thanks.  One more question.  If I insert a row higher than the identity counter for my table, will that identity value be skipped ny SQL Server in the future or will I end up with a duplicate key situation?

    Thanks again.
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    no, the value will not be skipped.
    but the insert trying to use that value will fail IF the field is defined as primary key.
    it will NOT retry a second time with the same value after that (the identity value has then been "consumed"

    check also out: DBCC CHECKIDENT:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now