Solved

Sql Server give ID's

Posted on 2011-09-27
11
191 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:joygomez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36713999
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)


0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36714029
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]
0
 

Author Comment

by:joygomez
ID: 36714038
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
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:joygomez
ID: 36714065
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
0
 

Author Comment

by:joygomez
ID: 36714086
what is test3
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36714148
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?
0
 
LVL 53

Accepted Solution

by:
Huseyin KAHRAMAN earned 500 total points
ID: 36714171
if you dont have any other column, we may need additional step to properly find & sort the record....

Step1:

create a new table: tmp(id - identity column, col1,col2)

then

do these






set identity_insert Intuit_Staging_Invoice on;

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

delete from Intuit_Staging_Invoice;

set identity_insert Intuit_Staging_Invoice off;

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

0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36714174
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

0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36714180
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

0
 

Author Closing Comment

by:joygomez
ID: 36714184
Thanks,
got it to work....
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 36714188
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

0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question