473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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

2 2823
nbiswas
149 100+
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
Nbiswas, this was just what i needed, worked like a charm!
Thanks a lot for your help :)
Dec 10 '09 #3

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

Similar topics

3
by: Gunnar Vøyenli | last post by:
Hi (SQL Server 2000) I have an existing table (t) with a column that is NOT an identity column (t.ID), but it has manually inserted "row numbers". I want to make this column become an identity...
5
by: grzes | last post by:
MS SQL Server 2000. My case is: I have the table T with primary key calling __recid int without identity property. This table includes a lot of records (about 1000000). I need to convert __recid's...
2
by: JJA | last post by:
I have fixed length records (167 bytes) in a .txt file and want to load this data exactly as is into a staging table where I hope to be able to later get at selected columns using the SUBSTRING...
2
by: valerio | last post by:
Hi all, I've some problem to import data to DB2 using the db2move and db2look tools. Follow the problem : I have exported data from db2 v. 7 database on windows server, using the db2look and...
3
by: dusty | last post by:
Hi, I'll try to simplify the problem: I created a table "TestTable" in a database on the SQL server. The first column, 'id', is the primary key with a auto-increment identity. I want to work...
1
by: smauldin | last post by:
Creating a table with an identity column works fine create table test(a integer, id integer generated always as identity ) When I attempt to add an identity column it fails. create table...
4
by: shorti | last post by:
Can anyone explain in greater (and more comprehensive) detail what the RESTART option does in the ALTER TABLE table ALTER COLUMN statement. This is the description in Info Center: RESTART or...
2
by: bbawa1 | last post by:
Hi, I have a table in which I have four columns. ID (primary Key) FirstName, LastName, Email I want to add another column as @@identity in this already existing
7
ak1dnar
by: ak1dnar | last post by:
Hi this is my first visit to the MSSQL forum with a question. Let me explain the scenario, I have a table say clients table with the structure like id,foo,etc.. and lots of records on it. But...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.