Database -- Auto Number reset
2 July 2008 - 10:32pm | 
Bob
Some times, there is a need to insert records into a table that has a auto number increment column. If you are needing to load one or many records and have to preserve an ID you need to turn off the auto numbering. Another reason might be that you need a zero record for a data warehouse. Below are two examples. One My Sql -- which has a few more lines of code. The other is Microsoft SQL Server.
| My SQL | |
| Code | Description |
| create table Table1 ( Table1ID integer unsigned not null auto_increment, Description varchar(255) not null, primary key (Table1ID));
|
Create example table. MySQL number starts at 1 |
| insert into Table1 (Table1Id, Description) values (0,'zero');
|
Insert a record. In this case a ID of zero. |
| select * from table1;
Table1ID Description -------- ----------- 1 Zero |
As you can see, the ID is 1 and it is to be 0. If the ID was any other number than 0, it would have taken. For example, if the ID was 5, 5 would have been inserted. But the next ID is now 6. |
| update Table1 set Table1ID = 0 where Table1ID = 1; | Update the record and reset the ID back to 0. |
| alter table Table1 auto_increment=1; | Reset what the next auto number will be. |
| select * from table1;
Table1ID Description -------- ----------- 0 Zero
|
The desired result. |
| SQL Server | |
| Code | Description |
| create table Table1 ( Table1ID int not null identity, Description varchar(255) not null, primary key (Table1ID)); |
Create table. |
| Set Identity_insert Table1 ON | Turn off auto number. In sql server. You cannot issue an Update command to change an ID. |
| insert into Table1 (Table1Id, Description) values (0,'zero'); | Insert a record. In this case a ID of zero. |
|
select * from table1; Table1ID Description -------- ----------- 0 Zero |
The desired result. |
| Set Identity_insert Table1 OFF | Turn on the auto number. |
|
|
|
Tags: MySQL, SQL Server, identity, autonumber, auto_increment, reset, override.
Tags: