Reputation: 11
I use SQL Server to create a table and insert data for it, but why does an open id not 1 but 2?
create table test1
(
id int IDENTITY(1,1),
name varchar(255),
age int,
primary key (id),
constraint check_age check (age > 0 and age <= 150)
);
The following is the result of the display.
select * from test1;
Output:
+----+--------------+-----+
| id | name | age |
+----+--------------+-----+
| 2 | shenmeshenqi | 20 |
| 3 | shenmeshenq | 21 |
+----+--------------+-----+
Who can help me? Thank you
Upvotes: 1
Views: 2334
Reputation: 37525
Try this:
--store values from the table in temporary table
select * into #tempTable from test1
--remove all records from the table
delete test1
--reset identity to start from 1
dbcc checkident (test1, reseed, 0);
--insert data back to the table, now the records will be numbered starting with 1
insert into test1
select * from #tempTable
Upvotes: 2
Reputation: 993
The below command will make the next insertion have the identity value as 1 and after that 2 and so on, thus 2 and 3 will be duplicates. You may want to just truncate the table for now as it looks like a test table.
dbcc checkident (test1, reseed, 0);
Also, you can turn onidentity insert, then insert data with value 1 as id and then turn off the identity again, if required.
SET IDENTITY_INSERT dbo.Test1 ON
SET IDENTITY_INSERT dbo.Test1 OFF
Hope it helps.
Upvotes: 0