Adding a Column to act as Promary Key

Question

Post reply

Adding a Column to act as Promary Key

NoviceJatt

SSCommitted

Points: 1671
More actions
July 14, 2010 at 1:25 pm

#235116

My Table does not have a Primary key. There is no column with unique entries. So I was thinking of adding a new column that can act as primary key for the table. I was just thinking that Ill add a column in the begining which has INT values starting from 1,2,3......upto last row.
For Example:
ALTER TABLE table_name
ADD column_name datatype
My question is how can I populate this column with INT values 1,2,3 ....upto last row?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SkyBVI SSCoach Points: 15128 More actions · Answer 1

add a identity column..

its a self increment column

ALTER TABLE XYZ

ADD id_num int IDENTITY(1,1)

Regards
Sushant Kumar
MCTS,MCP

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 2

Try using Row_Number() Function then you can alter the table to make it a primary key

NoviceJatt SSCommitted Points: 1671 More actions · Answer 3

My Table Keeps growing. I add new data all the time to it. So if I use IDENTITY(1,1), then eveytime I update my data, I have to first DROP the Primary Key and Then DROP the IDENTITY Column, then add new data and again ADD IDENTITY Column and assign Primary Key, Which is not Practical. I'm using this data for Reporting Purpose. So can you guys give me any Ideas how to deal with this Primary Key Issue.

Thanks

Ben Teraberry SSChampion Points: 11845 More actions · Answer 4

Novicejatt (7/14/2010)
My Table Keeps growing. I add new data all the time to it. So if I use IDENTITY(1,1), then eveytime I update my data, I have to first DROP the Primary Key and Then DROP the IDENTITY Column, then add new data and again ADD IDENTITY Column and assign Primary Key, Which is not Practical. I'm using this data for Reporting Purpose. So can you guys give me any Ideas how to deal with this Primary Key Issue.
Thanks

The IDENTITY column is self-managing. The numbers will increment on insertion. There is no reason why you would need to drop the column and add it again. If you need a number 1 to X without any gaps, you can simply do that in a query using the ROW_NUMBER function and you do not need to worry about storing that information in the table.

There are some SQL people who feel strongly against the use of IDENTITY columns and who insist that primary keys should always be natural. While I understand a certain measure of their point, my personal experience is that the people who feel this way have their experience in smaller environments. There is a certain point in database size at which the benefits of use a tidy INT for the primary key is so undeniable as to outweigh any and all other related concerns.

*edit: And by the way, if you posted your table definitions and approximate size and growth rate of your table, you may get good advice about your primary key that you will not otherwise receive.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

NoviceJatt SSCommitted Points: 1671 More actions · Answer 5

My Table has about 25 Columns. Its originally a csv file, but I preformat it and save it as tab delimited text file. Then I use BULK INSERT to import data into a Table. But after adding identity column as primary key, I can no longer import data into table by BULK INSERT. It gives me row truncation error. Why is that?

Thanks

Ben Teraberry SSChampion Points: 11845 More actions · Answer 6

Open Books Online and look under the entry for 'BULK INSERT statement'. Within that, go down to the section 'KEEPIDENTITY' and you will find all the information you need.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

NoviceJatt SSCommitted Points: 1671 More actions · Answer 7

NoviceJatt

SSCommitted

Points: 1671

July 16, 2010 at 11:21 am

#1194248

Thank You Very Much

KoldCoffee SSCoach Points: 18971 More actions · Answer 8

I'm following this thread and went to the documentation. I found:

"To prevent SQL Server from assigning identity values while bulk importing data rows into a table, use the appropriate keep-identity command qualifier. When you specify a keep-identity qualifier, SQL Server uses the identity values in the data file. "

Is Novicejatt supposed to use the KEEPIDENTITY argument during BULK INSERT?

If no is it this set of instructions that apply, in which case I wouldn't know what to do:

"If the data file (file being imported) does not contain values for the identifier column in the table, use a format file to specify that the identifier column in the table should be skipped when importing data. SQL Server assigns unique values for the column automatically."

How would you do the latter ie. use a format file?

NoviceJatt SSCommitted Points: 1671 More actions · Answer 9

Hi,

My data file does not have IDENTITY Column. I created IDENTITY Column in a table to use it as a primary key. So now when I want to import data from a new data file, it gives me error as the data file itself doesnot have IDENTITY Column. So what would be the best way to solve this problem ?

Thanks

KoldCoffee SSCoach Points: 18971 More actions · Answer 10

http://msdn.microsoft.com/en-us/library/ms190393.aspx

Here's a link for "Format Files for Importing or Exporting Data". Haven't had a chance to read it through myself but looks promising. See how far you get and post your questions. Good luck!

KoldCoffee SSCoach Points: 18971 More actions · Answer 11

here's a link for 2008 SQL Server...

http://msdn.microsoft.com/en-us/library/ms178129.aspx

it even includes info for skipping a column during import which may help with the earlier problem you had in the other post.