Removing Identity property SQL2K5

What is the fastest way to remove identity property of a column.?
I traced Enterprise Manager way of doing, it creates a replicate of the
table and copies all rows and constratints to it and deletes old table and
renames the new table with the original column.This is taking long time
Please suggest.
thxThat's probably pretty much it. i doubt SQL Server can do it any other way.
Anything else would involve the same thing, but in different ways e.g.
DTSing the data into a duplicate table, dropping the old table and renaming
the other one.
"skg" <skg@.yahoo.com> wrote in message
news:%23RcwXTSRGHA.424@.TK2MSFTNGP12.phx.gbl...
> What is the fastest way to remove identity property of a column.?
> I traced Enterprise Manager way of doing, it creates a replicate of the
> table and copies all rows and constratints to it and deletes old table and
> renames the new table with the original column.This is taking long time
> Please suggest.
> thx
>|||I guess you could add another column, copy the data over using an UPDATE sta
tement, drop the old
column and rename the column. But the columns would no be in the same order
as they were originally.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ian Boyd" <admin@.SWIFTPA.NET> wrote in message news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gb
l...
> That's probably pretty much it. i doubt SQL Server can do it any other way
.
> Anything else would involve the same thing, but in different ways e.g.
> DTSing the data into a duplicate table, dropping the old table and renamin
g the other one.
> "skg" <skg@.yahoo.com> wrote in message news:%23RcwXTSRGHA.424@.TK2MSFTNGP12
.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTn$x4fRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I guess you could add another column, copy the data over using an UPDATE
>statement, drop the old column and rename the column. But the columns would
>no be in the same order as they were originally.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ian Boyd" <admin@.SWIFTPA.NET> wrote in message
> news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

removing identity property

How can i remove identity property of a particular table with sql command.
Thanks.Why do you need to do it? You don't like the increment? Or the seed? Check DBCC CHECKIDENT(...) in BOL.

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
>> Folks,
>> I am trying to alter my database thru SQL Scripts. I have a few tables
> where
>> I have columns having Identity Property. I now want to modify these
> columns
>> having no Identity Property. But I can't find SQL Statement that can do
>> this.
>> Can some one help?
>> Regards
>> Shailaindra Sharma
>>
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet? :)
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>> Why can't we do things like that which we can easily do in access. How
>> hard can it be for ms to add these things which will make life so easy.
>> Regards

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra Sharma
You cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>
|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>
|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> Why can't we do things like that which we can easily do in access. How hard
> can it be for ms to add these things which will make life so easy.
> Regards
>
|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...

Removing Identity Property

Folks,
I am trying to alter my database thru SQL Scripts. I have a few tables where
I have columns having Identity Property. I now want to modify these columns
having no Identity Property. But I can't find SQL Statement that can do
this.
Can some one help?
Regards
Shailaindra SharmaYou cannot remove this property... you must add another column, move the id
info to the new column, drop the old column, and rename the new column...
Or do the same thing at the table level... However,
If you go into SEM, select the table , right click and go into design, you
can simply select the column and uncheck Identity at the bottom... SEM will
generate a script to do this work for you - if there are lots of rows it may
take a while.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> Folks,
> I am trying to alter my database thru SQL Scripts. I have a few tables
where
> I have columns having Identity Property. I now want to modify these
columns
> having no Identity Property. But I can't find SQL Statement that can do
> this.
> Can some one help?
> Regards
> Shailaindra Sharma
>|||Why can't we do things like that which we can easily do in access. How hard
can it be for ms to add these things which will make life so easy.
Regards
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ewlf9KqZFHA.3048@.TK2MSFTNGP14.phx.gbl...
> You cannot remove this property... you must add another column, move the
> id
> info to the new column, drop the old column, and rename the new column...
> Or do the same thing at the table level... However,
> If you go into SEM, select the table , right click and go into design, you
> can simply select the column and uncheck Identity at the bottom... SEM
> will
> generate a script to do this work for you - if there are lots of rows it
> may
> take a while.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Shailaindra Sharma" <shailaindra@.abosoftware.com> wrote in message
> news:eIHMP%23pZFHA.3840@.tk2msftngp13.phx.gbl...
> where
> columns
>|||Consider proposing this:
sqlwish@.microsoft.com
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <John@.nospam.infovis.co.uk> wrote in message news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx
.gbl...
> Why can't we do things like that which we can easily do in access. How har
d
> can it be for ms to add these things which will make life so easy.
> Regards
>|||You mean no one has found it to be a problem as yet?
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:esZQgZsZFHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Consider proposing this:
> sqlwish@.microsoft.com
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23iv4mYrZFHA.2664@.TK2MSFTNGP15.phx.gbl...

Removing IDENTITY from existing column

I have been testing some code that has been inserting large amounts of data
into some tables. All of the tables have a Primary Key that is also an
Identity. After a bad run, I go through and DELETE all of the entries in
each table (I would TRUNCATE, but they all have at least three other tables
referencing them and you can't TRUNCATE a linked table). Only problem is
that the IDs are getting extremely large and make it harder for me to
determine if the code is working correctly. In the TSQL that I use to DELET
E
the contents of the tables, I would like to set the IDENTITY to NO, then bac
k
to YES so that the IDs increment from 1 again instead of some very large
number. The only way that I can see is to drop the column and then add it
again. I am not sure how well this would work since I have lots of foreign
keys referencing these PKs.
Thanks in advance.
--
Chris Lieb
UPS CACH, Hodgekins, IL
Tech Support Group - Systems/AppsDrop the IDENTITY and not the column, then put the IDENTITY back on the
column -- one way.
Owen
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||See "DBCC CHECKIDENT" in BOL.
AMB
"Chris Lieb" wrote:

> I have been testing some code that has been inserting large amounts of dat
a
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other table
s
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to DEL
ETE
> the contents of the tables, I would like to set the IDENTITY to NO, then b
ack
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of foreig
n
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Try: DBCC CHECKIDENT (tableName)
Look it ub in BOL.
"Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>I have been testing some code that has been inserting large amounts of data
> into some tables. All of the tables have a Primary Key that is also an
> Identity. After a bad run, I go through and DELETE all of the entries in
> each table (I would TRUNCATE, but they all have at least three other
> tables
> referencing them and you can't TRUNCATE a linked table). Only problem is
> that the IDs are getting extremely large and make it harder for me to
> determine if the code is working correctly. In the TSQL that I use to
> DELETE
> the contents of the tables, I would like to set the IDENTITY to NO, then
> back
> to YES so that the IDs increment from 1 again instead of some very large
> number. The only way that I can see is to drop the column and then add it
> again. I am not sure how well this would work since I have lots of
> foreign
> keys referencing these PKs.
> Thanks in advance.
> --
> Chris Lieb
> UPS CACH, Hodgekins, IL
> Tech Support Group - Systems/Apps|||Use
DBCC CHECKIDENT ('table_name',RESEED,0)
to reset the IDENTITY value.
David Portas
SQL Server MVP
--|||I tried to add IDENTITY to an existing column, and it didn't work. I was
told on this forum that it can't be done.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
> Drop the IDENTITY and not the column, then put the IDENTITY back on the
> column -- one way.
> Owen
> "Chris Lieb" <ChrisLieb@.discussions.microsoft.com> wrote in message
> news:27E1D52C-BE0C-425D-BCA2-04BB2A89B367@.microsoft.com...
>|||Strange. I do that all the time (using Enterprise Manager)...
Owen
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
>I tried to add IDENTITY to an existing column, and it didn't work. I was
>told on this forum that it can't be done.
>
> "Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
> news:O%23vzDYhYFHA.980@.TK2MSFTNGP12.phx.gbl...
>|||Strange that you use Enterprise Manager to make changes to table
structures :-)
Paul is in fact correct that you cannot do this with a single statement
in TSQL. One of the (many) problems with using Enerprise Manager is
that to achieve this it will drop your table, constraints and indexes,
recreate and then copy all your data over. Definitely not recommended
on a system that's in use!
David Portas
SQL Server MVP
--|||That's because behind the scenes EM will jump through the hoops
of creating a temp table copy of the original table, creating a
new table with identity column assigned, inserting data from the
the temp table, and then dropping the temp table. There's no
easy way of doing it through Query Analyzer other than
what I've outlined.
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...
an
then
large
add
>|||EM does a lot of work behind the scenes.
A SIMPLE example on a table with no foreign key references where Identity is
removed:
It creates a new table, copies the data, drops the old table and renames the
new one.
If there where foreign key references, EM has to handle this too.
In EM, you have an icon in the table design view to save the script when you
make a change and before committing it.
Try it (on a test table of course).
"Owen Mortensen" <ojm.NO_SPAM@.acm.org> wrote in message
news:e$HrUihYFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Strange. I do that all the time (using Enterprise Manager)...
> Owen
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:eI6kqdhYFHA.3960@.TK2MSFTNGP10.phx.gbl...

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariHi,
You need to drop and create the table to remove IDENTITY property.
If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.
Thanks
Hari
SQL Server MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have
> data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Here's the script:
ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO
Make sure there's nobody making changes to this data while you are running
this script.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
Hari|||Thank you verymuch for your suggetion.
Hari
"Narayana Vyas Kondreddi" wrote:
> Here's the script:
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari
>
>

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariI think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
thsi will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||I think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
this will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||I think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
this will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Hi,
You can not remove the IDENTITY property using Alter table command.
Only way is export data into a new table using
1. SELECT * INTO newtable from existing table
2. create new table with out identity
3. insert the data from new table
4. drop the existing table and rename the new table using sp_rename
Enterprise manager internally almost does the above steps to remove identity
property.
Thanks
Hari
SQL Server MVP
"sajeevp" <sajeev.padmanabhan@.gmail.com> wrote in message
news:1122522742.838595.315250@.f14g2000cwb.googlegroups.com...
>I think
> --
> alter table [dbo].[EventLog]
> alter column [RecordId] [int]
> --
> this will work
>
>
> Hari wrote:
>|||>I think
> --
> alter table [dbo].[EventLog]
> alter column [RecordId] [int]
> --
> thsi will work
No, it will not. Did you try it?|||that was a wrong post .. sorry|||Thanks for your suggetion.
Hari
"Hari Pra" wrote:

> Hi,
> You can not remove the IDENTITY property using Alter table command.
> Only way is export data into a new table using
> 1. SELECT * INTO newtable from existing table
> 2. create new table with out identity
> 3. insert the data from new table
> 4. drop the existing table and rename the new table using sp_rename
> Enterprise manager internally almost does the above steps to remove identi
ty
> property.
> Thanks
> Hari
> SQL Server MVP
>
>
> "sajeevp" <sajeev.padmanabhan@.gmail.com> wrote in message
> news:1122522742.838595.315250@.f14g2000cwb.googlegroups.com...
>
>

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.??
Hari
Hi,
You need to drop and create the table to remove IDENTITY property.
If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.
Thanks
Hari
SQL Server MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have
> data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.??
> Hari
|||Here's the script:
ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO
Make sure there's nobody making changes to this data while you are running
this script.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.??
Hari
|||Thank you verymuch for your suggetion.
Hari
"Narayana Vyas Kondreddi" wrote:

> Here's the script:
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.??
> Hari
>
>

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable'
) = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_C
I_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariHi,
You need to drop and create the table to remove IDENTITY property.
If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.
Thanks
Hari
SQL Server MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have
> data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTabl
e') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Here's the script:
ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO
Make sure there's nobody making changes to this data while you are running
this script.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable'
) = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_C
I_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
Hari|||Thank you verymuch for your suggetion.
Hari
"Narayana Vyas Kondreddi" wrote:

> Here's the script:
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTabl
e') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari
>
>

Removing identity column

We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?

The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?

Open up the Northwind DB an select identity NO for the OrderID

The script that Enterprise Manager creates is this (Click on the 3rd Icon from the left, left side of the scissors)

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM dbo.Orders TABLOCKX')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
GRANT REFERENCES ON dbo.Orders TO public AS dbo
GRANT SELECT ON dbo.Orders TO public AS dbo
GRANT UPDATE ON dbo.Orders TO public AS dbo
GRANT INSERT ON dbo.Orders TO public AS dbo
GRANT DELETE ON dbo.Orders TO public AS dbo
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT

so as you can see there is a lot that has to be done behind the scenes

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Removing duplicate data

I have a table that I need to remove duplicates from and the table includes
an identity column. The table contains 12 fields but our business rules are
that only 3 of the fields can make the record a duplicate. We would like to
keep the record with the min identity column. I have done this in the past,
but it was about 5 years ago and I did not insert the dups into another
table, I did it strictly with a query. Can anybody please help me out with
some code to take care of this or any suggestions on a better way of doing
this? So something along the lines of grouping the data by the fields that
we are interested in and then deleting the records where the identity column
is greater than the min identity column.
Thanks.This is the duplicates along with the
minimum id per group
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1
Join this to the original table
to give the ids of the duplicates
excluding the minimum id.
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
Put it all together to delete them
DELETE
FROM sometable
WHERE id IN (
SELECT a.id
FROM sometable a
INNER JOIN (
SELECT MIN(id),Col1,Col2,Col3
FROM sometable
GROUP BY Col1,Col2,Col3
HAVING COUNT(*)>1) b(id,Col1,Col2,Col3) ON b.id<>a.id
AND b.Col1=a.Col1
AND b.Col2=a.Col2
AND b.Col3=a.Col3
)|||Do you need to export the removed duplicates to another table?
Try something like this:
INSERT INTO RemDup (...) SELECT ... FROM MyTab AS t1 WHERE EXISTS (SELECT
NULL FROM MyTab AS t2 WHERE t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND
t1.Col2=t2.Col2 AND t1.ID>t2.ID)
DELETE FROM MyTab AS t1 WHERE EXISTS (SELECT NULL FROM MyTab AS t2 WHERE
t1.Col1=t2.Col1 AND t1.Col2=t2.Col2 AND t1.Col2=t2.Col2 AND t1.ID>t2.ID)
HTH,
Axel Dahmen
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
> I have a table that I need to remove duplicates from and the table
includes
> an identity column. The table contains 12 fields but our business rules
are
> that only 3 of the fields can make the record a duplicate. We would like
to
> keep the record with the min identity column. I have done this in the
past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
that
> we are interested in and then deleting the records where the identity
column
> is greater than the min identity column.
> Thanks.|||Here's an example. This will delete all but 1 of the dupes. You will need to
change table / col name accordingly
DELETE FROM _Holding_table
WHERE EXISTS(SELECT NULL FROM _Holding_table s1
WHERE s1.PhoneNumber= _Holding_table.PhoneNumber
and s1.PK_Holding_TableID > _Holding_table.PK_Holding_TableID)
HTH. Ryan
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:32ACE9AA-67CE-454C-9711-956595CB7A8D@.microsoft.com...
>I have a table that I need to remove duplicates from and the table includes
> an identity column. The table contains 12 fields but our business rules
> are
> that only 3 of the fields can make the record a duplicate. We would like
> to
> keep the record with the min identity column. I have done this in the
> past,
> but it was about 5 years ago and I did not insert the dups into another
> table, I did it strictly with a query. Can anybody please help me out
> with
> some code to take care of this or any suggestions on a better way of doing
> this? So something along the lines of grouping the data by the fields
> that
> we are interested in and then deleting the records where the identity
> column
> is greater than the min identity column.
> Thanks.|||This is untested, perhaps you wrap this in a transaction first:
DELETE FROM SomeTable S
WHERE Idcolumn NOT IN
(
SELECT MIN(idcolumn)
FROM SOMETABLE S2
GROUP BY FirstColumn1,FirstColumn2,FirstColumn3
)
HTH, Jens Suessmeyer.