Post reply

Insert Value in Identity Column

  • July 7, 2010 at 10:10 am

    #222911

    How do I account for the value of an identity column when doing an insert to a table. A NOT NULL vaule won't work and throws an error. Col4 is an identity column int (1, 1)

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3]

    ,[Col4])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL'

    ,)

  • July 7, 2010 at 10:13 am

    #1189727

    SQLEE (7/7/2010)

    How do I account for the value of an identity column when doing an insert to a table. A NOT NULL vaule won't work and throws an error. Col4 is an identity column int (1, 1)

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3]

    ,[Col4])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL'

    ,)

    Just omit anything being inserted to that column:

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL')

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 8, 2010 at 12:10 am

    #1190011

    WayneS (7/7/2010)

    SQLEE (7/7/2010)

    How do I account for the value of an identity column when doing an insert to a table. A NOT NULL vaule won't work and throws an error. Col4 is an identity column int (1, 1)

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3]

    ,[Col4])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL'

    ,)

    Just omit anything being inserted to that column:

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL')

    If u want to insert a Value to the Identity Table u can use

    Set Identity_Insert MyTable ON

    this command will allow you to insert value to the identity column

    and then

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3]

    ,[Col4])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL'

    ,<any integer value>)

    Set Identity_Insert MyTable OFF

    To set back the table column to identity column

    and then u can use to insert values except identity column

    INSERT INTO MyTable

    ([Col1]

    ,[Col1]

    ,[Col3])

    VALUES

    ('XXXX'

    ,'XXXX'

    ,'NULL')

    [font="Courier New"]-- Alban Lijo <SQL Rookie> :-)[/font]

  • July 8, 2010 at 7:12 am

    #1190217

    Thanks, ignoring the identity column was the answer.:-)

Viewing 4 posts - 1 through 3 (of 3 total)

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