CODEWITHSUNDEEP
sql-serverprimary-keybulkinsert
lmsasu
lmsasu

Reputation: 7583

Add primary key to a table with many records

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.

How can I add it?

Upvotes: 27

Views: 21228

Answers (6)

dpimente
dpimente

Reputation: 527

I know this is older but I ran into this and solved it a different way. Assuming you're using SSMS 2008, you could go into Tools --> Options

Under 'Designers' then 'Table and Database Designers', change the 'Transaction time-out after' value. Default is 30, 0 is for infinite. Try to create the Primary Key after increasing the value.

This tends to be a lot quicker than; creating a new DB, copying the data, deleting the old Db and renaming the new db to the old db. After you're done applying the Primary Key you can set the timeout back to 30 too - if you wish, your choice.

Update Adding a screenshot enter image description here

Upvotes: 24

HotTester
HotTester

Reputation: 5778

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

Upvotes: 11

momobo
momobo

Reputation: 1775

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.

Upvotes: 0

masoud ramezani
masoud ramezani

Reputation: 22940

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.

Upvotes: 0

Chris
Chris

Reputation: 1364

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.

Upvotes: 40

Lucero
Lucero

Reputation: 60236

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

Upvotes: 2

Related Questions