June 30, 2008 at 4:23 am
Ex:--
create table testing1
(
cIDint 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..
July 1, 2008 at 8:06 am
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.
July 2, 2008 at 1:00 am
My dear frnd thanks for reply.....
i m not using enterprise Manager..
how can i do in query analyzer.
send me some exp:-
July 2, 2008 at 6:28 am
Using Query Analyzer
ALTER TABLE testing1 DROP COLUMN Cid
GO
ALTER TABLE testing1 ADD Cid INT IDENTITY (1,3) NOT NULL
GO
July 3, 2008 at 2:28 am
WHEN I M DOING.. THAN..
MY ALL COLUMN TestID values Changes..
July 3, 2008 at 6:20 pm
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?
July 4, 2008 at 2:09 am
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)
July 4, 2008 at 6:52 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply