remove identity column using sql script

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

SQL Server 2000 Forums

Transact-SQL (2000)

remove identity column using sql script

Author

Topic

sampro
Starting Member

4 Posts

Posted - 2008-01-22 : 10:37:53

i m using sqlserver 2005
i have a table that has the primary key set as identity
i want to make that off insert one row and value that column that is set as identity and then again make that on and i want to do that through sql script.

please can anyone specify it i need that urgently

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-22 : 10:41:15

set identity_insert table_name on
insert......
set identity_insert table_name off

Madhivanan

Failing to plan is Planning to fail

sampro
Starting Member

4 Posts

Posted - 2008-01-23 : 07:45:22

i have tried it but after setting the identity off when i insert i still get the error that column with identity cant insert values.

in short that command is not working of setting the identity off.
Please advice.

Thanks.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-23 : 07:51:44

I believe you should go through this link to know about identity_insert and think of solution.
http://technet.microsoft.com/en-us/library/aa259221(SQL.80).aspx
http://www.sqlteam.com/article/understanding-identity-columns

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-23 : 07:51:47

quote:
Originally posted by sampro

i have tried it but after setting the identity off when i insert i still get the error that column with identity cant insert values.

in short that command is not working of setting the identity off.
Please advice.

Thanks.

You need to set it on then off
Read my previous reply

Madhivanan

Failing to plan is Planning to fail

sampro
Starting Member

4 Posts

Posted - 2008-01-23 : 09:20:53

i am diong this

set identity_insert FINISH on
insert into FINISH values('-1','None Selected')
set identity_insert FINISH off

and getting error this

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'FINISH' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Can you please tell me where i am wrong

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-01-23 : 09:32:43

insert into FINISH (MYCOLUMN1, MYCOLUMN2) values('-1','None Selected')

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-23 : 13:18:35

quote:
Originally posted by sampro

i am diong this

set identity_insert FINISH on
insert into FINISH values('-1','None Selected')
set identity_insert FINISH off

and getting error this

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'FINISH' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Can you please tell me where i am wrong

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources