June 17, 2003 at 5:54 am
Is it possible to switch the identity poperty to On/Off with ALTER TABLE, on an existing Column!!!
Tom
June 17, 2003 at 6:29 am
Wouldn't you just redclare the column without IDENTITY in the definition?:
ALTER TABLE MyTable
ALTER COLUMN MyIdentityColumn INT NOT NULL
June 17, 2003 at 6:34 am
OK, but the column is also used as primary key, and is a part of a foreign key. I just wondered if there is an easy way...
June 17, 2003 at 6:37 am
I try'd that but whatif you want to switch it back on?
June 17, 2003 at 6:43 am
quote:
I try'd that but whatif you want to switch it back on?
You'd note where the IDENTITY seed value left off; do a SELECT @seed = MAX(IDENTITY_COL) + 1 FROM MyTable. Then,
ALTER TABLE MyTable
ALTER COLUMN MyIdentityColumn INT NOT NULL IDENTITY(@seed, 1)
Of course, you can't use variables like that in DDL -- just make a note of @seed and replace the value in the DDL statement...
Edited by - jpipes on 06/17/2003 06:44:31 AM
June 17, 2003 at 6:50 am
I get an error: incorrect syntact near identity. The problem is that 'identity(seed, increment)' can only be used to define a identity column. Or am I wrong.
Best regards Tom
June 17, 2003 at 6:56 am
Hmmm, looks like there is a much easier solution to this one (thanks to my illustrious colleague, RonKyle...):
Look up Books On Line for "SET IDENTITY_INSERT"
You can turn identity on and off for a table...
HTH,
jay
June 17, 2003 at 6:59 am
You can add an IDENTITY property by using ALTER TABLE, but you cannot remove an IDENTITY property. The way EM handles it is it creates a new table without the IDENTITY property, moves the data, drops the old table, and renames the new table to the old table name.
SET IDENTITY_INSERT works, to switch the IDENTITY property on and off. Keep in mind that when you switch it back on, it'll choose a seed based on the highest number in the identity column.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 17, 2003 at 7:18 am
This is my problem:
I have an existing table, in that table there is a integer type column.
I want to set the indentity to on, on that column with a script.
When I try 'Alter Table - Alter Column' I get an error message: 'incorrect syntac near identity'
Tom
June 17, 2003 at 7:21 am
1) Is there an IDENTITY column already on the table?
2) Can you post your syntax?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 17, 2003 at 7:25 am
There is no identity on that column nor any other column in the table
Syntax:
alter table HO_categorie
alter column p_cat int not null identity(1,1)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'identity'.
Kind regards Tom
June 17, 2003 at 7:50 am
Try removing the NOT NULL
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 17, 2003 at 7:54 am
Still same error (syntax error!)
Tom.
June 17, 2003 at 8:03 am
Wait. Are there values in the table already for the column in question?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 17, 2003 at 8:08 am
Yes, there are values.
But has that fact anything to do with a syntax error?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply