CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
user1438150
user1438150

Reputation: 175

Populate new ID Column with Sequential Identifiers

I need to populate a column nvarchar(12) with a unique sequence starting at "PM1000000000" and increment by 1 for each row. There is no identity column or Primary key (edit) to loop around which makes this problem quite challenging as most of the example I found use an identity column to loop around

I found an example, example 3 on MSDN at https://support.microsoft.com/en-us/kb/111401 but it doesn't show incrementing a row

Can anyone help me populate this field so that I can make this a primary key? Note: this table has 60 million rows but I'm open to any ideas at this point to just get this working

Upvotes: 1

Views: 127

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use ROW_NUMBER():

SELECT
    ID = 'PM' + CONVERT(NVARCHAR(10), ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1000000000 - 1)
FROM <YourTable>

UPDATE Statement

;WITH Cte AS(
    SELECT *,
        RN = 'PM' + CONVERT(NVARCHAR(10), ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1000000000 - 1)
    FROM TestData
)
UPDATE Cte SET ID = RN

Upvotes: 6

Aeroradish
Aeroradish

Reputation: 371

You can use a cursor. It will be slow, though. I've used cursors for datatables with ~1MM records, but not 60MM. You can find an example of a cursor here.

https://msdn.microsoft.com/en-us/library/ms180169.aspx

Upvotes: 0

Related Questions