insert into identity column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

insert into identity column

Author

Topic

askmanojjain
Starting Member

10 Posts

Posted - 2007-09-21 : 05:58:49

I have migrated my application from Access to sql 2005 express. In access autonumber (identity) field was random number.

fter conversion now i want identity field to be "increment". management studio does nt allow.
I have created a new database with increment identity column but during importing data using insert into statement it give error

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

please suggest how to do it??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-21 : 06:02:12

[code]SET IDENTITY_INSERT ON
GO

INSERT INTO TABLE SELECT .....[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 06:41:50

quote:
Originally posted by harsh_athalye

SET IDENTITY_INSERT ON
GO

INSERT INTO TABLE SELECT .....

SET IDENTITY_INSERT OFF
Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Madhivanan

Failing to plan is Planning to fail

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-09-22 : 01:20:28

Hi,
SET IDENTITY_INSERT tablename ON
INSERT TABLENAME(ID,NAME)VALUES(1,'PRIYA')
Here id is the IDENTITY column.
tablename is the name of the table
NOTE:You must mention the identity column name using INSERT
statement.
Happy Programming!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-24 : 01:49:22

quote:
Originally posted by kiruthika

Hi,
SET IDENTITY_INSERT tablename ON
INSERT TABLENAME(ID,NAME)VALUES(1,'PRIYA')
Here id is the IDENTITY column.
tablename is the name of the table
NOTE:You must mention the identity column name using INSERT
statement.
Happy Programming!

As I specified it is better to turn identity off at the end

Madhivanan

Failing to plan is Planning to fail

askmanojjain
Starting Member

10 Posts

Posted - 2007-10-17 : 16:27:34

Thanks all of you,
I am sorry for delayed reply

askmanojjain
Starting Member

10 Posts

Posted - 2007-10-17 : 16:30:31

Please also suggest what will be the new values in the identity column after i have inserted many rows with negative and possitive values at random from my access program

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-17 : 16:45:21

SQL Server will find the latest value (MAX) in the table and increment from there. So if there were 1,2,5,6,7 and you inserted 3, SQL Server will start from 8.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 16:47:00

Well you should run DBCC CHECKIDENT after manually making changes to the table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-18 : 05:28:33

Also dont worry on having gaps if you use identity column

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-18 : 05:31:42

Yes you have to.
If you try to insert a record which breaks a rule/constraint, the current identity value is lost and next insert will get following value even if previous value is not used.

CREATE TABLE	#Temp
		(
			RowID INT IDENTITY(1, 1),
			CountryID INT CHECK(CountryID = 46)
		)

INSERT	#Temp
SELECT	46

SELECT * FROM #Temp

INSERT	#Temp
SELECT	45

SELECT * FROM #Temp

INSERT	#Temp
SELECT	46

SELECT * FROM #Temp

DROP TABLE #Temp

E 12°55'05.25"
N 56°04'39.16"

askmanojjain
Starting Member

10 Posts

Posted - 2007-10-18 : 14:58:04

Thanks all of you
Your support will help me migrate from access to sql express
reagrds and thanks again
MANOJ JAIN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources