SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 remove identity column using sql script		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sampro
Starting Member

4 Posts
Posted - 01/22/2008 :  10:37:53  Show Profile  Reply with Quote
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

India
22460 Posts
Posted - 01/22/2008 :  10:41:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

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


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sampro
Starting Member

4 Posts
Posted - 01/23/2008 :  07:45:22  Show Profile  Reply with Quote
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.
Go to Top of Page

sunil
Constraint Violating Yak Guru

India
282 Posts
Posted - 01/23/2008 :  07:51:44  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22460 Posts
Posted - 01/23/2008 :  07:51:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

sampro
Starting Member

4 Posts
Posted - 01/23/2008 :  09:20:53  Show Profile  Reply with Quote
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
Go to Top of Page

jhocutt
Constraint Violating Yak Guru

USA
385 Posts
Posted - 01/23/2008 :  09:32:43  Show Profile  Visit jhocutt's Homepage  Send jhocutt an AOL message  Reply with Quote
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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2111 Posts
Posted - 01/23/2008 :  13:18:35  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000