August 19, 2009 at 3:14 am
Hi,
create table employee(employee_rid int primary key, employee_name varchar(100))
insert into employee values(1, 'John')
insert into employee values(2, 'Joy')
insert into employee values(3, 'Troy')
insert into employee values(4, 'Alex')
After executing the above queries,
Can I set IDENTITY with seed value 5 to the particular field "employee_rid"?
Regards
Prileep
August 19, 2009 at 3:47 am
Not using an ALTER statement no.
Simplest way is to make a copy of the table with the identity seeded at 5, copy the data into it (using IDENTITY_INSERT ON), drop the original table and rename the new table i.e.
CREATE TABLE dbo.Tmp_employee
(
employee_rid int NOT NULL IDENTITY (5, 1),
employee_name varchar(100) NULL
)
GO
SET IDENTITY_INSERT dbo.Tmp_employee ON
GO
INSERT INTO dbo.Tmp_employee (employee_rid, employee_name)
SELECT employee_rid, employee_name FROM dbo.employee
GO
SET IDENTITY_INSERT dbo.Tmp_employee OFF
GO
DROP TABLE dbo.employee
GO
EXECUTE sp_rename N'dbo.Tmp_employee', N'employee', 'OBJECT'
GO
August 19, 2009 at 4:14 am
Just to add to kevriley's solution... if you use this method, don't forget to re-add any constraints and triggers you may have had on the original employee table.
August 19, 2009 at 4:47 am
Ian Scarlett (8/19/2009)
Just to add to kevriley's solution... if you use this method, don't forget to re-add any constraints and triggers you may have had on the original employee table.
Good point, well made!
August 19, 2009 at 4:54 am
kevriley (8/19/2009)
Ian Scarlett (8/19/2009)
Just to add to kevriley's solution... if you use this method, don't forget to re-add any constraints and triggers you may have had on the original employee table.Good point, well made!
Painfully made... forgot about triggers once.:Whistling:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply