Create a new table of unique values from a field in an existing table

Question

Post reply

Create a new table of unique values from a field in an existing table

garethmann101

SSCommitted

Points: 1980
More actions
September 11, 2008 at 9:38 am

#212561

I want to create a new table of just the unique values in a table. For example, say I have a huge table of addresses, and the "city" of each address is stored in a seperate field. I want a new table called "Cities" that would have one row for each city that exists in the addresses table (but no duplicates).
I know I could write some elaborate code in Visual Basic that would actually build arrays of unique values and loop through each record checking against the current values in the array before adding a new one, but I was hoping there was a straight forward SQL statement that could be used.
Thanks in advance.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Seems like a homework question.

What have you tried so far?

garethmann101 SSCommitted Points: 1980 More actions · Answer 2

This is not a homework question, I am looking for the SQL Command to write this in an SQL statement. I know this is a simple question, that is why I put it in the "newbie" section, any help would be really appreciated.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Give us the DDL statments along with the DDL statements to load the tables.... we'll show you how to do it.

garethmann101 SSCommitted Points: 1980 More actions · Answer 4

There really are no "DDL statements" as such. I am building the database by importing a bunch of seperate excel tables into Access and then importing the access database into SQL Server 2000.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

September 11, 2008 at 10:45 am

#870478

Cool, do that then we'll be able to help you.

garethmann101 SSCommitted Points: 1980 More actions · Answer 6

garethmann101

SSCommitted

Points: 1980

September 11, 2008 at 11:10 am

#870501

Its done

garethmann101 SSCommitted Points: 1980 More actions · Answer 7

Ok i think i am almost there, I have just found out about the "Distinct" keyword, this allows me to show just the distinct values in an SQL statement:

Select Distinct (fieldname) from (tablename)

Now I just need to work out how to save that output into a new table

garethmann101 SSCommitted Points: 1980 More actions · Answer 8

Ok I have got it:

CREATE TABLE new_table

AS (SELECT Distinct (fieldname) FROM (tableName));

So simple!

Yet it took me an hour of research on the internet, but it should only take an expert 5 seconds to work out.

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 9

I do believe that researching "SELECT INTO" should get you where you want to be.

Kyle

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Like this:

INSERT Into {target_table} ( ..target_columns.. )

SELECT Distinct ..source_columns..

From {source_table}

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

garethmann101 SSCommitted Points: 1980 More actions · Answer 11

Thank you Kyle,

That was exactly what I needed, the "create table as" was not working, but "select into" worked perfect.

Kyle Neier , SSCrazy Points: 2252 More actions · Answer 12

I'm glad it helped.

I'll now add that the table you just created did not inherit any properties other than names and data types from the source table. Any indexes or constraints (including defaults) that you may need will have to be manually applied.

This method is a quick and dirty way to get a table created, but as you familiarize yourself with SQL Server, I suggest you strive to create your tables first then fill them (using the aforementioned INSERT statement). You'll have more control and understand better what is going on.

Good luck!

Kyle

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

garethmann101 (9/11/2008)Yet it took me an hour of research on the internet, but it should only take an expert 5 seconds to work out.

But if we just hand you the solution without your having tried to solve it youself, how much do you truely learn? That's why you were asked to show what you had already done to solve your problem.

If you have tried and failed, and show what you have done, and where you are having problems, many of us are more than willing to jump in and help increase your knowledge.

😎

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

I still don't see any code to replace the city name to cityid in the base table, nor indexing, nor foreign keys strategies in place...

You guys still have much work to do!!!

BTW, I always do a manual data cleaning after a task like this. There's always someone who screwed up the data (New-York, New-York City, NYC, N-Y City...). That needs to be manually cleansed (script can only point you in the right direction, manual intervention is necessary here).

Good luck.