asked on

Sql Server give ID's

I have the first two columns in a table. i want to create a third column just like I have to give it an ID. Notice that depending on if the value of the second column repeats itself for the same value in column one, i want to increment by 1, like i have shown you in the grid here. So essentially i want to create the 3rd column (ID) in code.

Just can't figure out how to do this in code. Can someone help.

Column 1      Column 2      ID
46      23      1
46      26      1
46      27      1
46      23      2
47      27      1
47      24      1
49      24      1
50      24      1
50      24      2
50      27      1

HainKurt

this query will give you third column

select col1, col2, row_number() over (partition by col1, col2 order by ???) rn
from myTable

then join this with original and update your col3 (you may not need this step since you can always get it with query above)

HainKurt

to update it

update t
set t.col3=x.rn
from test3 t inner join
(select col1, col2, sort, row_number() over (partition by col1, col2 order by [sort]) rn from test3) x
on t.col1=x.col1 and t.col2=x.col2 and t.[sort]=x.[sort]

joygomez

ASKER

HainKurt,
Can you be a bit more specific with the syntax. I am quite new to sql...:( what is the ??? and rn for.
Thanks a lot

joygomez

ASKER

ok, i am getting errors... can you make this dummy proof for me please.

this is my table - Intuit_Staging_Invoice
this is the first column in the above table - IntuitQboInvoiceId
this is the second column in the above table - IntuitQboItemId
the third column is what needs to be updated - InvoiceItemInternalId

joygomez

ASKER

what is test3

HainKurt

here it is

update t
set t.InvoiceItemInternalId=x.rn
from Intuit_Staging_Invoice t inner join
(select IntuitQboInvoiceId, IntuitQboItemId, sort, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by [sort]) rn from Intuit_Staging_Invoice) x
on t.IntuitQboInvoiceId=x.IntuitQboInvoiceId and t.IntuitQboItemId=x.IntuitQboItemId and t.[sort]=x.[sort]

we need another column in your table properly sort and find the record, I used [sort] column... are these the only 3 columns in your table? no other column? like create date or some other column?

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

oops, sorry, identity insert should be turned on tmp

set identity_insert tmp on;

insert into tmp(col1,col2) select * from Intuit_Staging_Invoice;

set identity_insert tmp off;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by id) from tmp;

Open in new window

HainKurt

we can do it without and identity column on tmp

create a new table: tmp (IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)

insert into tmp select *, row_number() over (order by 1) from Intuit_Staging_Invoice;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by InvoiceItemInternalId) from tmp;

Open in new window

joygomez

ASKER

Thanks,
got it to work....

HainKurt

or we can use select into to create table automatically & insert in one shot

select * into tmp
from (
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by 1) from Intuit_Staging_Invoice
) x;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by InvoiceItemInternalId) from tmp;

Open in new window