CODEWITHSUNDEEP
sqlsql-server
arcee123
arcee123

Reputation: 211

Adding a surrogate key to existing table (SQL SERVER)

I have a large table of 225 million rows. It does NOT have any primary keys. It's just a sheet of data.

I want to add a TableID, and set surrogate keys to it. I've created the TableID bigint column.

What's the fastest way to put in key values (1, 2,3, etc...)?

This is just horrendous as the optimizer sees it.

with CTE as (
    select ROW_NUMBER() 
      OVER (ORDER BY GEOID, A_ID, Zip, latitude, longitude) as rn,
    GID,
    A_ID,
    Zip,
    latitude,
    longitude from tableA
) update Table a set a.TableID = CTE.rn 
    where a.GID = CTE.GID
    and a.A_ID = CTE.A_ID
    and a.Zip = CTE.Zip
    and a.latitude = CTE.latutude
    and a.longitude = CTE.longitude;

Thanks.

Upvotes: 1

Views: 4230

Answers (2)

The Impaler
The Impaler

Reputation: 48780

You don't mention the database, so I'll assume PostgreSQL. Then, why not use a sequence?

create sequence my_new_table_id;

alter table tablea add column tableid bigint;

update tablea set tableid = nextval('my_new_table_id');

Other databases offer similar solutions, all of them pretty fast.

Upvotes: 1

arcee123
arcee123

Reputation: 211

I used this example:

Update records in table from CTE

to generate this:

;with CTE as (
     select ROW_NUMBER() OVER (ORDER BY GEOID, A_ID, Zip, latitude, longitude) as rn,
     TableID,
     GID,
     A_ID,
     Zip,
     latitude,
     longitude from tableA
) update CTE set CTE.TableID = CTE.rn

took alot away from the Optimizer. Hope it helps someone else.

Upvotes: 0

Related Questions