Post reply

Adding a Column to act as Promary Key

  • 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

  • July 14, 2010 at 2:17 pm

    #1193114

    add a identity column..

    its a self increment column

    ALTER TABLE XYZ

    ADD id_num int IDENTITY(1,1)

    Regards
    Sushant Kumar
    MCTS,MCP

  • July 14, 2010 at 2:34 pm

    #1193122

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

  • July 14, 2010 at 3:41 pm

    #1193156

    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

  • July 14, 2010 at 6:50 pm

    #1193207

    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]

  • July 14, 2010 at 9:16 pm

    #1193223

    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

  • July 15, 2010 at 5:56 pm

    #1193837

    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]

  • July 16, 2010 at 11:21 am

    #1194248

    Thank You Very Much

  • July 16, 2010 at 3:02 pm

    #1194380

    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?

  • July 16, 2010 at 3:30 pm

    #1194397

    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

  • July 16, 2010 at 3:47 pm

    #1194408

    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!

  • July 16, 2010 at 3:51 pm

    #1194412

    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.

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

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