Post reply

Statement for inserting ar rowid to an existing table

  • July 13, 2009 at 7:38 pm

    #202245

    Hi,

    I want to add a rowid to an existing table in sql server 2005. Is the statement below correct?

    alter table tablename add rowID int identity(1,1)

    Once that rowid is added, how would the existing rows be numbered?

    Thank you for your time.

  • July 13, 2009 at 8:54 pm

    #1024218

    If you mean that you want to add a column, this will work. It will give you an auto-incrementing column for all new rows. However it does not change existing rows or add values for them.

    A table can have only one column with an identity value.

  • July 13, 2009 at 9:02 pm

    #1024219

    Actually, it will number the existing rows in physical order starting at the seed, which you have specified to be 1.

    Try this out for an example:

    CREATE TABLE #tmp (TextData varchar(max))

    BULK INSERT #tmp FROM "your text file"

    select * from #tmp

    ALTER TABLE #tmp ADD RowID int identity(1,1)

    select * from #tmp

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 13, 2009 at 10:27 pm

    #1024233

    Thank you for your help.

    I will try what you mentioned.

    When you say "your text file", would that be my table name?

  • July 14, 2009 at 9:46 am

    #1024517

    /

  • July 15, 2009 at 2:55 pm

    #1025373

    info (7/13/2009)

    Thank you for your help.

    I will try what you mentioned.

    When you say "your text file", would that be my table name?

    No, that means some text file on the local drive that the server is on... it will load it into the temp table.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 17, 2009 at 8:17 am

    #1026201

    Thank you.

    It worked!!!

    I appreciate your help

Viewing 7 posts - 1 through 7 (of 7 total)

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