If we want next record to have identity as 35 we need to run following T SQL script in Query Analyzer.
If table has to start with an identity of 1 with the next insert then the table should be reseeded with the identity to 0.
Learn something new every day (BTW I tested this on SQLExpress; despite the SWITCH TO it doesn't use partitioning, apparently).
Note that the table has to match pretty-much exactly (indexes, FKs, etc)@t.j when creating the temp table script out the source table including all indexes and constraints.
DBCC CHECKIDENT can reseed (reset) the identity value of the table.
He is one man who has lots of ideas and helps people from all over the world with passion using this community as platform.
In SQL Server, you set the identity property of a column, in Oracle you create a Sequence, and in Microsoft Access you create an Auto Number column.
A Data Column can also be used to generate automatically incrementing values by setting the Auto Increment property to true.
My answer is only one more (create table, switch, update, switch back, drop) It is presumably you are more familiar with these steps so they look less convoluted.
Updating can be much more efficient then delete insert when many rows are involved. If a /*Define table with same structure but no IDENTITY*/ CREATE TABLE Temp ( ID INT PRIMARY KEY, X VARCHAR(10) ) /*Switch table metadata to new structure*/ ALTER TABLE Test SWITCH TO Temp; /*Do the update*/ UPDATE Temp SET ID = ID 1; /*Switch table metadata back*/ ALTER TABLE Temp SWITCH TO Test; /*ID values have been updated*/ SELECT * FROM Test /*Safety check in case error in preceding step*/ IF NOT EXISTS(SELECT * FROM Temp) DROP TABLE Temp /*Drop obsolete table*/ CREATE SEQUENCE Seq AS INT START WITH 1 INCREMENT BY 1 CREATE TABLE Test2 ( ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY, X VARCHAR(10) ) INSERT INTO Test2(X) SELECT 'Foo' UNION ALL SELECT 'Bar' UNION ALL SELECT 'Baz' UPDATE Test2 SET ID =1 Very slick.
However, there is an easy way to accomplish this action. This table is vacant, with no records, as you can see in the following screenshot. You will notice that T-SQL is used by SQL Server to make this change. Dwl Entry ( Id INT IDENTITY(1,1), User Name VARCHAR(100), Config Item VARCHAR(100), Component VARCHAR(100), Time Logged DECIMAL(13,2) )INSERT INTO dbo.
After you make the change for Identity property from No to Yes, on top in tools box, you will see Generate Change Script. Dwl Entry(User Name, Config Item, Component, Time Logged) VALUES(‘Bubai’,’AFOTA’,’Documentation’,’3′) INSERT INTO dbo.
If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want.
An example is if you wanted to start numbering your ID column at 1000 instead of 1.
This is the T-SQL Script that SQL Server will use to make this change. Dwl Entry(User Name, Config Item, Component, Time Logged) VALUES(‘Bubai’,’GENERAL’,’Troubleshooting’,’3′) INSERT INTO dbo.
Unfortunately, I cannot expand this dialogue box further to show you the complete script, so I have copied this script below. Dwl Entry(User Name, Config Item, Component, Time Logged) VALUES(‘Bhanu’,’AFOTA’,’Code Change’,’3′) INSERT INTO dbo.