Add primary key to a table with many records

Question

Hi,

I have a table in SQL Server 2005 containing 10000054 records; these records are inserted through a bulk insert operation. The table does not contain a primary key and I want to have one. If I try to modify the table's structure, adding a new column, PK, set as int with isidentity, the management console gives me a warning:

"Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible."

then outputs error:

" Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "

I want a PK into my table.. give me my PK!!

How can I add it?

Thanks

Answer 1

Try to generate the SQL script and issue it from a SQL Query window.

Answer 2

If in Management Studio you set the primary key in Design view (without saving), when you next right click you have an option "Generate Change Script" - this option is also available on the "Table Designer" menu at the top.

That provides the raw SQL (safely wrapped in a transaction) which you can copy to clipboard, take that over to run as a New Query (button top left, or File > New > Query with Current Connection), paste it in, select the right DB and execute the query.

Answer 3

make a new table with exact schema and make the desired column the primary key. Now using select to insert ( eg here) copy the records from one table to another. When completed then delete the old table and rename this new table to the desired name.

Answer 4

you can make a table with primary key. when insert data with bcp you can use a format file to map columns. format file for bulk copy is a good solution. with this solution you insert data with identity information.

Answer 5

From you response to David I get you do not have a suitable column for primary key. In this case you could add an int identity as the last column of the table, bulk insert would continue to work.

As the table is loaded with a bulk insert the best option would be truncate the table (truncate table MYTBL); alter it, adding the ID (alter table TBL add ID int identity primary key) and re issue the bulk load.

If you cannot rebulk the table you could use stamp or hassan solution to alter the table.