|My son, the toilet paper mummy|
I had a user come to me and said "We need the table to generate the ID automatically, but it's not doing it. Can you fix it?"
Sure, I'm a DBA. I can fix anything. Or at least, I will create an approximation of fixing close enough that you don't know the difference.
The data had been imported awhile ago from an Access database. So the existing data had id's assigned. Typically, Identity columns start at one and increment by one whenever a row is added.
Luckily, SQL identity columns allow you to set the seed (aka the starting number) and the increment value (so you can change the increment interval). I could find the seed by doing a Max(ID) +1. You can insert into an Identity column, but you can't update an identity column.
I thought about creating a new Identity column and copying the ID values into it.
Problem one, by default, one cannot insert into a column that is designed to be filled automatically. It's just not done. I learned that SQL allows us to ignore that constraint. by using SET IDENTITY_INSERT
When I tried to update the values in the new Identity column, I learned that YOU CAN"T UPDATE and Identity column.
My solution was to create a script that created a new table with our identity value and insert from one into the other.
The process is comprised of four steps
- Script the existing table to your query window
- Find the seed by finding the maximum ID and adding one. (you can script this as well)
- Modify the ID field to be an Identity with your seed
- Do an INSERT INTO SELECT command
- Drop the old table
- Rename the new table
- Recieve praise for doing the impossible.
-- This creates the tableUSE [yourDatabase GO
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
CREATE TABLE [dbo].[AutoNumberTemp](
-- This allows us to insert into our Identity column[ID] [numeric](18, 0) IDENTITY(1807,1) NOT NULL, [userid] [nvarchar](50) NULL, ) ON [PRIMARY]go
-- This copies data from our original table into our new tableSET IDENTITY_INSERT [dbo].[AutoNumberTemp] ON
INSERT INTO [dbo].[AutoNumberTemp] ( [ID] ,[userid]
FROM [dbo].[AutoNumber] WITH (TABLOCKX)
/* Braver DBA's may omit the backup and replace it with a drop table */-- Turn off Identity_Insert, because it's not a good idea to leave on
SET IDENTITY_INSERT [dbo].[AutoNumberTemp] OFF
exec sp_rename '[dbo].[AutoNumber]','[AutoNumber_old]';
/* Now we rename the new table with the old tables names */
exec sp_rename 'AutoNumberTemp','AutoNumber';go