How do I add PRIMARY KEY/IDENTITY attributes to existing column in MS SQL 2000?

Hello all,

I'm having a problem with Microsoft SQL Server 2000 and am hoping to benefit from the wisdom of the experts out there.  Here's what's going on...

After making a stupid mistake in a script I wrote and having to restore one of the tables from previous backups using EMC Retrospect, I realized that, while Retrospect restores all the data in a given table without a problem, attributes such as default values, primary key, etc. are stripped from all columns.

I was able to add the default values back to all columns using the query analyzer.  But one of the columns in the restored table, called "acctid", is an identity column and primary key, and I do not know how to add those attributes back to the column, so that it will continue to auto increment its value every time a record is added to the table.

I'm not terribly familiar with Microsoft SQL Server, so if anyone can provide me with insight on how to write a SQL query that will set this column back to the primary key/identity column, I will be extremely grateful.

- Tristan
alphactAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
You will have to have exclusive use of the table for a while.

First, script out the existing table.  Keep that script.

Then rename the table:
EXEC sp_rename 'tableName', 'tableNameTemp'

Edit the script to make the "acctid" column an IDENTITY column (and a PK if desired).  Run the script.

Now, reload the table *but keeping the existing acctid values*.

SET IDENTITY_INSERT tableName ON
INSERT INTO tableName (acctid, ...rest of columns...)
SELECT *
FROM tableNameTemp
SET IDENTITY_INSERT tableName OFF

-- if PK designation has not been done yet
ALTER TABLE tableName
ADD PRIMARY KEY (acctid)

DROP TABLE tableNameTemp


You can then use the table as usual, with the identity in place.
0
 
k_rasuriCommented:
alter table and add colum with identity constraint

ALTER TABLE Yourtable
ADD Yourcolumn IDENTITY(1,1) NOT NULL
0
 
k_rasuriCommented:
or to make it a primary key

ALTER TABLE Yourtable
ADD Yourcolumn IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

when you add this column, it will automatically assingn the unique keys for each column. in this way you can restore your primary key...but make sure that the data is same as before in your new table. even if the order is changed..you wont get teh same primary keys for rows
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
David ToddSenior DBACommented:
Hi,

When recently commenting on a similar question
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22703487.html#a19519262
I found that SQL wont allow adding not null columns without a default to an existing table.

I wasn't trying this with identity's so I'm not sure if the above will work or not.

Regards
  David
0
 
Scott PletcherSenior DBACommented:
>> First, script out the existing table. <<
Just the table definition, not the rows themselves :-) .  [Just to make sure no misunderstandings :-) .]
0
 
alphactAuthor Commented:
Scott, thank you very much for your help.  I greatly appreciate it.  Thank you, also, to everyone else who commented.  I now have the table back to normal (as in, how it was before the Retrospect backup software messed it up), and I've learned a couple new things in the process.

Thanks again.

- Tristan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.