Search code examples
sql-serverazure-sql-server

Insert New Primary Key to Table with data already inside

I have table with million of records and is requirement to add new int field 'Id' as primary key with auto incremental. Now I have manage to do to tables with reasonable small amount of data but some of table holds million of records and throw time out error. The database is Azure SQL

'MyTable(dbo)' table
- Unable to modify table.  
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the 
server is not responding.
The statement has been terminated.
Solution

  • I make an example code that maybe you can reference:

    CREATE TABLE dbo.doc_exz (column_a INT not null, column_b INT) ;
GO
INSERT INTO dbo.doc_exz VALUES (7,7) ;
INSERT INTO dbo.doc_exz VALUES (7,7) ;
INSERT INTO dbo.doc_exz VALUES (7,7) ;
INSERT INTO dbo.doc_exz VALUES (7,7) ;
GO

ALTER TABLE dbo.doc_exz ADD id int IDENTITY(1,1) not null 
GO

ALTER TABLE doc_exz ADD PRIMARY KEY (id)
GO        
SELECT * FROM dbo.doc_exz ;
GO

DROP TABLE dbo.doc_exz ;
GO

    The output of the query:

    enter image description here

    If you still get the timeout error. You could follow the suggestion of @Larnu:

    1. Create a new version of the table
    2. INSERT the existing data into it
    3. then DROP the old table and rename new one.

    You can use BULK INSERT for the large amount data.