Menu
i recently imported some old data to a new SQL table and forgot to set the ID column to auto increment.
Now i have several "NULL"s in the column named ID.
Is there a way I can update all ID columns with an unique ID automatically? sadly i have no clue how
After that I will be able to set the column to INT and Auto Increment.
Thank you for your help.
841
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
Here's the syntax of ALTER TABLE statement, ALTER TABLE table_name MODIFY column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY; In the above statement, you need to specify the table_name and column_name. Here's the SQL statement to add AUTO INCREMENT constraint to id column.
Go to Identity Specifications and explore it. Make (Is Identity) row as Yes and by default Identity Increment row and Identity Seed row become 1. In case we want to automatically increase the value of this column by 2 (like 1, 3, 5, 7 etc.) then change the value of Identity Seed to 2.
Try using Sequence Object for Sql Server 2012
create Sequence Sq as int
minvalue 1
cycle;
update table set Column=NEXT VALUE FOR Sq where Column is null
The easiest way is to remove your old ID Column and add a new column ID with :
ALTER TABLE [dbo].[myTable] DROP COLUMN ID
ALTER TABLE [dbo].[myTable] ADD ID int IDENTITY
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
