Remove Identity

[Toeen]

I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen

[Hermilson Tinoco]

Hi

You can use something as this

Create tha table with Identity...

CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL
[f_city_desc] [varchar] (50) DEFAULT (''
) ON [PRIMARY
G

And to Modify the column, erasing the property of the Identity.

Alter TABLE City
Alter column f_city_id [int] NOT NUL

Hermilson T

*************************************************************

I have created an identity column in a table.Now i want to remove th
identity property from the column by using T-SQL script
Can anyone tell How
Thanking you
Toee

[Sebastian K. Zaklada]

Create tha table with Identity....
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL

You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
example:

BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT

sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

[Dandy WEYN]

Or you can use a select into statement


select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'

--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT

http://www.dandyman.net

Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql

Post by Sebastian K. Zaklada

Create tha table with Identity....
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL

You can't clear the IDENTITY property from the table column using the ALTER
TABLE statement. You have to re-create the database and move the data, for
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.

[Dan Guzman]

Post by Dandy WEYN
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity

SELECT INTO will retain the identity property under SQL 2000. However,
Toeen can specify a column list to either exclude the identity column or
CAST it to a regular numeric value like the example below.

SELECT
CAST(MyIdentityColumn AS int),
MyOtherData
INTO newtable
FROM sourcetablewithidentity
--
Hope this helps.

Dan Guzman
SQL Server MVP

Post by Dandy WEYN
Or you can use a select into statement
select * into newtable from sourcetablewithidentity
drop table sourcetablewithidentity
exec sp_rename dbo.TemporaryTable', 'YourTable'
--
Dandy Weyn, Belgium
MCSE, MCSA, MCDBA, MCT
http://www.dandyman.net
Check my SQL Server resource pages (currently under construction)
http://www.dandyman.net/sql

Post by Sebastian K. Zaklada

Create tha table with Identity....
CREATE TABLE [City]
( [f_city_id] [int] IDENTITY (1, 1) NOT NULL ,
[f_city_desc] [varchar] (50) DEFAULT ('')
) ON [PRIMARY]
GO
And to Modify the column, erasing the property of the Identity.
Alter TABLE City
Alter column f_city_id [int] NOT NULL

You can't clear the IDENTITY property from the table column using the

ALTER

Post by Sebastian K. Zaklada
TABLE statement. You have to re-create the database and move the data, for
BEGIN TRANSACTION
CREATE TABLE dbo.TemporaryTable
(
id int NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.YourTable)
EXEC('INSERT INTO dbo.TemporaryTable(id)
SELECT id FROM dbo.YourTable TABLOCKX')
GO
DROP TABLE dbo.YourTable
GO
EXECUTE sp_rename N'dbo.TemporaryTable', N'YourTable', 'OBJECT'
GO
COMMIT
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no

rights.

[Eric N Hanson]

This is probably not what you want, but there is a chance it will help you.
If all you want to do is insert some values in the identity column yourself
directly, you may be able to get by by doing

set identity insert <tablename> on

Then you can insert your values. When you are done, set identity insert to
off.

Eric

Post by Toeen
I have created an identity column in a table.Now i want to remove the
identity property from the column by using T-SQL script.
Can anyone tell How?
Thanking you!
Toeen