Insert Value in Identity Column

Question

Insert Value in Identity Column

SQLEE

Ten Centuries

Points: 1077
More actions
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'
,)

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

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

Alban Lijo Old Hand Points: 302 More actions · Answer 2

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]

SQLEE Ten Centuries Points: 1077 More actions · Answer 3

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