By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

How to add an identity column to a table with existing data

P: 2
Hello, this is my first post here, i hope i'll find this forum usefull.

Although i did a search about my question, i didnt find what i need , because the question isnt exactly what it sounds.

Here's my scenario

I have a table that doesnt have an identity column, but does have unique rows depending on a combination of two columns.
the table's name is carowner and the two columns i am talking about are iteid,cusid and they are both of datatype int. what i need to do is to add to the existing data of the table a linenumber per iteid. for example i could have the following two records
iteid | cusid
6668 | 1
6668 | 2
6669 | 1
6669 | 4

what i wanna do is add a column so that the above data would look like this

iteid | cusid | Linenum
6668 | 1 | 1
6668 | 2 | 2
6669 | 1 | 1
6669 | 4 | 2

So i need to number the records depending on the column iteid and i need to do that for the existing data but also for every new record inserted into the table.
I was thinking this could be done by adding a trigger to the table but i am not sure on how to do this.

Any help woould be greatly appriciated, so many thanx in advance.
Dec 9 '09 #1

✓ answered by nbiswas

Try this

Expand|Select|Wrap|Line Numbers
  1. declare @t table(itemid int,cusid int)
  2. insert into @t 
  3.     select 6668,1 union all
  4.     select 6668,2 union all
  5.     select 6669,1 union all
  6.     select 6669,4

Query:(Sql server 2005+)
Expand|Select|Wrap|Line Numbers
  1. select 
  2. t.*
  3. ,ROW_NUMBER() over(partition by itemid order by itemid) Linenum
  4. from @t t
Query (For Sql Version less than 2005)

Expand|Select|Wrap|Line Numbers
  1. select *, ( select count(*)
  2. from @t t2
  3. where t2.itemid = t1.itemid
  4. and t2.cusid <= t1.cusid
  5. ) as Linenum
  6. from @t t1

Output:

itemid cusid Linenum
Expand|Select|Wrap|Line Numbers
  1. 6668     1               1
  2. 6668            2               2
  3. 6669            1               1
  4. 6669            4               2
  5.  
Hope this helps

Share this Question
Share on Google+
2 Replies


nbiswas
100+
P: 149
Try this

Expand|Select|Wrap|Line Numbers
  1. declare @t table(itemid int,cusid int)
  2. insert into @t 
  3.     select 6668,1 union all
  4.     select 6668,2 union all
  5.     select 6669,1 union all
  6.     select 6669,4

Query:(Sql server 2005+)
Expand|Select|Wrap|Line Numbers
  1. select 
  2. t.*
  3. ,ROW_NUMBER() over(partition by itemid order by itemid) Linenum
  4. from @t t
Query (For Sql Version less than 2005)

Expand|Select|Wrap|Line Numbers
  1. select *, ( select count(*)
  2. from @t t2
  3. where t2.itemid = t1.itemid
  4. and t2.cusid <= t1.cusid
  5. ) as Linenum
  6. from @t t1

Output:

itemid cusid Linenum
Expand|Select|Wrap|Line Numbers
  1. 6668     1               1
  2. 6668            2               2
  3. 6669            1               1
  4. 6669            4               2
  5.  
Hope this helps
Dec 9 '09 #2

P: 2
Nbiswas, this was just what i needed, worked like a charm!
Thanks a lot for your help :)
Dec 10 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.