CODEWITHSUNDEEP
sqlsql-server-2008
None
None

Reputation: 5670

Forgot to add primary key column as Identity

I had created a table like this

    CREATE TABLE [dbo].[sydShopOrder](
    [rowNumber] [varchar](50) NULL,
    [firstName] [varchar](50) NULL,
    [lastName] [varchar](50) NULL,
    [employeeNumber] [varchar](50) NULL,
    [productID] [varchar](50) NULL,
    [shopID] [varchar](50) NULL,
    [location] [varchar](50) NULL,
    [address] [varchar](50) NULL,
    [department] [varchar](50) NULL,
    [datestamp] [date] NULL
) ON [PRIMARY]

I was intended to make [rowNumber] as primary key and make it identity column with auto increment. But I forgot to do it. And now the database is up and running in live environment. I had found this bug very late. Is there any way that I can fix this? Make [rowNumber] column as identity and auto increment it?

Current screenshot of table looks like this

enter image description here

Upvotes: 0

Views: 1384

Answers (1)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

You can drop [rownumber] and then add with identity

Alter Table [dbo].[sydShopOrder] Drop Column rownumber
Go
Alter Table [dbo].[sydShopOrder]
Add rownumber Int Identity(1, 1)
Go

If you want to populate the identity field for existing data, better to create another temporary table, and keep all the records of [dbo].[sydShopOrder] in that. After that truncate [dbo].[sydShopOrder] and then insert the values from that temp table to [dbo].[sydShopOrder] 

CREATE TABLE #temp  ([firstName] [varchar](50) NULL,
    [lastName] [varchar](50) NULL,
    [employeeNumber] [varchar](50) NULL,
    [productID] [varchar](50) NULL,
    [shopID] [varchar](50) NULL,
    [location] [varchar](50) NULL,
    [address] [varchar](50) NULL,
    [department] [varchar](50) NULL,
    [datestamp] [date] NULL)

INSERT INTO #temp 
SELECT [firstName],[lastName],[employeeNumber],
       [productID], [shopID],[location],
       [address],[department],[datestamp]
FROM [dbo].[sydShopOrder]

TRUNCATE TABLE [dbo].[sydShopOrder]

INSERT INTO [dbo].[sydShopOrder]
SELECT * FROM #temp

Here is a sample SQLFIDDLE

Upvotes: 2

Related Questions