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.
When the ALWAYS keyword is specified it is not possible to explicitly include values for the identity column in INSERT OR UPDATE statements.
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 247 ms.Given that we now have an identity column we must first enable identity_insert so we can populate our own Customer Id’s.SET IDENTITY_INSERT [dbo].[Resource Order Customers Orders] ON; GO INSERT INTO dbo.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.