hi,
Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.
I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.
The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?
Any help whould be greatly appreciated.
Thanks,
Prashanth ReddyOriginally posted by Prashanth
hi,
Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.
I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.
The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?
Any help whould be greatly appreciated.
Thanks,
Prashanth Reddy
You have 2 options that spring to mind:
1. You can use TRUNCATE TABLE to accomplish this, but you will lose all data and as its a non-logged operation nothing will be written to the transaction log. I should add that if the table is referenced by a foriegn key constraint then you will not be able to use this method.
Usage: TRUNCATE TABLE tablename
2. The other option is to use DBCC CHECKIDENT to reset the seed:
Usage: DBCC CHECKIDENT (tablename, RESEED, 0)
macka.|||thanks for the response.|||thanks for the response.
I have patient table with PatientID as auto increment field
with
"Identity Increment" as 1 and
"Identity seed" as 1
I can chane Identity Seed using
DBCC checkident (patient,reseed,4)
How do i change the "Identity Increment" value to 10?
Prashanth|||The following script should work. In this example it resets the seed to 4 and increment to 10.
If the table is referenced by foreign key constraints then you will need to drop them before this script is run and add them back after it completes.
ALTER TABLE Demo
DROP CONSTRAINT PK_Demo
ALTER TABLE Demo
DROP COLUMN DemoID
ALTER TABLE Demo
ADD DemoID INT IDENTITY(4,10)
ALTER TABLE Demo ADD CONSTRAINT
PK_Demo PRIMARY KEY CLUSTERED
(
DemoID
) ON [PRIMARY]
macka.
Showing posts with label seed. Show all posts
Showing posts with label seed. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
Script to alter Identity, Identity Seed, Identity Increment
Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
>> Because I have to do this more then once for several Tables, I do not
>> want to do this manually but through a Script.
>> The thing is: do not have a clue how to do it.
>> Cheers
>> Marcel
>>
>> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
>> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>>
>
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
>> Because I have to do this more then once for several Tables, I do not
>> want to do this manually but through a Script.
>> The thing is: do not have a clue how to do it.
>> Cheers
>> Marcel
>>
>> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
>> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>>
>
Script to alter Identity, Identity Seed, Identity Increment
Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>
Subscribe to:
Posts (Atom)