Updating identity column

CREATE TABLE [dbo].[Resource Order Customers Orders] ( [Order Id] INT NOT NULL , [Customer Id] INT NOT NULL , [Resource Id] INT NOT NULL , [Quantity] INT NULL , [Created] DATETIME NOT NULL , [Created By] VARCHAR(20) NOT NULL , [Modified] DATETIME NULL , [Modified By] VARCHAR(300) NULL , [Order Date] DATETIME NULL , [Approved Date] DATETIME NULL , [Processed Date] DATETIME NULL , [Rejected Date] DATETIME NULL ); We’ve performed our data analysis and now want to formalize this into a production table.

To do this we need to create a primary key and we’ve flagged Order ID as a column that we want to convert into an identity type.

This can be seen here (SET @id = id = @id 1) where we are making the @id value and the id column equal to the current @id value 1.

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 247 ms.

To do this we use select into and move everything into a temporary table: DROP TABLE dbo.[Resource Order Customers Orders]; CREATE TABLE [dbo].[Resource Order Customers Orders] ( [Order Id] IDENTITY(1, 1) NOT NULL , [Customer Id] INT NOT NULL , [Resource Id] INT NOT NULL , [Quantity] INT NULL , [Created] DATETIME NOT NULL , [Created By] VARCHAR(20) NOT NULL , [Modified] DATETIME NULL , [Modified By] VARCHAR(300) NULL , [Order Date] DATETIME NULL , [Approved Date] DATETIME NULL , [Processed Date] DATETIME NULL , [Rejected Date] DATETIME NULL ); We now need to re-insert our data from the temporary table into the production one.

You’ve created a Microsoft SQL Server table, added some data and then realised that integer column you created would be much better off as an identity column.

You go to alter the table but SQL Server won’t allow you to change your integer column into an identity one without re-creating the table and losing the data… The following is my regular workaround to the problem.

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.

352

Leave a Reply