SQL Primary Key Required on existing table with nu unique field

Question

0.00/5 (No votes)

See more:

Hi,

I have a SQL table with a large quantity of data on it. There is no primary key and there is no unique field on the table.

I copied the structure and some data and was able to add a primary key field through design, but when I try the same on the original table it times out.

The only other way I know would be to add a column, populate the field manually and then set the key, but there are over a million records.

Is there another way of doing this please?

Posted 16-Feb-11 5:20am

milenalukic

Add a Solution

Comments

milenalukic 16-Feb-11 12:00pm

The data is a live dump from a legacy system which is still live. I'm trying to add the field through design directly from the server and it does not seem to time out. Has been running for about 30 mins now. The cursor is a good idea if this doesn't work. Normalising the data is my ultimate goal but i think that would involve rewriting the legacy system I have to live with!!

milenalukic 17-Feb-11 8:04am

Adding the field directly from the server worked. It took some time but got there at last.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jim lahey · Answer 1 · 2011-02-16T05:24:00

Solution 1

I'm not 100% sure of the T-SQL you'd need, but you could create your new table complete with primary key and then run a cursor over your old table, inserting each row you find in the old table into the new one

Posted 16-Feb-11 5:24am

jim lahey

Chris Meech · Answer 2 · 2011-02-16T05:39:00

Another approach may be to create a number of new tables, each with their own primary key and then copy the data from your existing table into these other tables. The number of new tables would reflect a normalizing of your data and how many you choose would be dependant upon how the data relates to each other.