Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IDENTITY Expand / Collapse
Author
Message
Posted Monday, June 30, 2008 4:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34
Ex:--
create table testing1
(
cID int identity(1,1) not null,
cname varchar(50)

)

Add 10 record........ after that.. 4 or 8 record delete..
My table like that.....
CId cName
1 Sunil
2 Sunil
3 Sunil
5 Sunil
6 Sunil
7 Sunil
9 Sunil
10 Sunil

than IDENTITY is off....
i want change the CId IDENTITY (1,3)..
how can i alter the table..

Post #525789
Posted Tuesday, July 1, 2008 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
You could using SSMS
a). In design mode drop the column cID
b). Save the table
c). Go back into design mode insert a new column named cID
d). Set the column data type to int, do not allow NULLS
e). In the lower frame (column properties) expand "Identity specification" and then set (Is identity) to Yes and input values for "Identity increment" and "identity seed"
f). Close and save the table design window.

This should do it.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #526656
Posted Wednesday, July 2, 2008 1:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34
My dear frnd thanks for reply.....

i m not using enterprise Manager..

how can i do in query analyzer.

send me some exp:-
Post #527053
Posted Wednesday, July 2, 2008 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
Using Query Analyzer

ALTER TABLE testing1 DROP COLUMN Cid
GO

ALTER TABLE testing1 ADD Cid INT IDENTITY (1,3) NOT NULL
GO



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #527236
Posted Thursday, July 3, 2008 2:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34
WHEN I M DOING.. THAN..
MY ALL COLUMN TestID values Changes..
Post #527841
Posted Thursday, July 3, 2008 6:20 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
dost.kumar your original question was:
i want change the CId IDENTITY (1,3)..
how can i alter the table..

which is what resulted following my suggestion.
Do you want your existing entries to maintain the values they had before changing the identity increment, with only new values having the new identity values or?
Can you provide an example of what you want as your result?


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #528441
Posted Friday, July 4, 2008 2:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 3:50 AM
Points: 10, Visits: 34
create table testing1
(
cID int identity(1,1) not null,
cname varchar(50)

)

select *From testing1
insert into testing1 values ('sunil2')
insert into testing1 values ('sunil6')
insert into testing1 values ('sunil7')
insert into testing1 values ('sunil8')
insert into testing1 values ('sunil9')
insert into testing1 values ('sunil10')
insert into testing1 values ('sunil11')

Result
cID cname
----------- --------------------------------------------------
1 sunil
2 sunil6
3 sunil7
4 sunil8
5 sunil9
6 sunil10
7 sunil11

than delete record 2,4,6

Than Result
cID cname
----------- --------------------------------------------------
1 sunil
3 sunil7
5 sunil9
7 sunil11

than Drop column Cid and create again


ALTER TABLE testing1 DROP COLUMN Cid
GO

ALTER TABLE testing1 ADD Cid INT IDENTITY (1,3) NOT NULL
GO
select *from testing1

result
cname Cid
-------------------------------------------------- -----------
sunil 1
sunil7 4
sunil9 7
sunil11 10

(4 row(s) affected)
Post #528537
Posted Friday, July 4, 2008 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
If the result:
cname Cid
---------- -----------
sunil2 1 |
sunil7 3 |--- Values after deleting Cid values 2,4,6
sunil9 5 |
sunil11 7 |
sunil20 10 |
sunil60 13 | --- Cid values after altering the
sunil70 16 | identity increment using
sunil80 19 | Management Console
sunil90 22 |
sunil100 25 |
sunil110 28 |

If this is what you want the only way to alter the identity increment is to use the management console. Open the table in design view and alter the identity increment, close and save the revised table.
To the best of my knowledge there is no way to use T-SQL commands to accomplish the results as shown.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #528657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse