Assign an ID to the rows in a database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Assign an ID to the rows in a database

Author

Topic

Japboix1
Starting Member

24 Posts

Posted - 2011-07-07 : 13:11:11

Hey, I'm sorry if this question has been asked before, but say I have a database with a LOT of entries. Like, in the thousands. It also has several columns of information, like city, province, stuff like that. And I want each row/entry to have a unique id. However, for certain rows, if certain data is the same as in a nother row, I want the two rows to have the same id. So, is there a way to do this without manually searching through every row and changing the ones that match up with other rows?

For example;
I have...
Province__City___Postal Code____________
ON |Toronto | M5J2W1
BC |Vancouver | f4D2S3
BC |Vancouver | F5D2E4
QC |Montreal | E3G2T2

And I want every entry with a different City name to have a different ID. But anything that is in the same city should have the same ID. And I can't just add this in manually, there are thousands of entries which takes too long to do.

Also, if this isn't actually possible, please say so. XD

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-07 : 13:26:26

From a design perspective your table(s) should have a Primary Key and/or a Unique Constraint that defines and limits your data to what your business says should be a single row only. That would prevent these "duplicates" from getting into your table(s).

To clean up your existing table you can do a SELECT on those columns that should be unique and GROUP by the same column(s). The result can go into a table with an identity column (that would be a surrogate row identifier) or you could just let the columns be the "natural key".

Be One with the Optimizer
TG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 13:28:05

Here is an example of how you could do it using dense rank.


-- Test data
CREATE TABLE #tmp (col1 INT, col2 INT);
INSERT INTO #tmp
   SELECT 8,4 UNION ALL  
   SELECT 5,6 UNION ALL 
   SELECT 5,7 UNION ALL 
   SELECT 8,4

-- add the new id column
ALTER TABLE #tmp ADD id INT ;

-- update the id column.
WITH cte AS
(
   SELECT DENSE_RANK() OVER (ORDER BY col1,col2) RN, -- all your colmuns
   * FROM #tmp
)
UPDATE cte SET id = rn; 

SELECT * FROM #tmp;

-- clean up.
DROP TABLE #tmp;

again!! Not my day! I am not posting anything more today

Japboix1
Starting Member

24 Posts

Posted - 2011-07-07 : 14:16:28

@TG
The only thing with that is that I don't want to delete any of the data, even if it is exactly the same. All of the data needs to be the same, just with a new column added that has an id for each distinct entry.

@sunitabeck
Basically the same as above, so does this method allow me to add the ids without deleting the duplicates?

Sorry about not menitoning that. I sort of forgot.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-07-07 : 14:26:57

[code]SELECT city, province, [postal code],
dense_rank() over (order by city, province)
from yourTable[/code]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-07 : 14:28:23

What I posted should not delete any rows in your table. You should be able to copy the code I posted and run it to see what it does and then adapt it to your needs.

However, even though I am a completely trustworthy person (honest! I am!!) if I were you, I would not trust me. Instead, I would test any code I copy from the internet and understand it before applying to my production code.

Japboix1
Starting Member

24 Posts

Posted - 2011-07-07 : 14:40:40

Ah, ok then. Thank you XD

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources