Post reply

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

  • 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.

  • September 11, 2008 at 10:06 am

    #870440

    Seems like a homework question.

    What have you tried so far?

  • September 11, 2008 at 10:10 am

    #870446

    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

  • September 11, 2008 at 10:14 am

    #870451

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

  • September 11, 2008 at 10:19 am

    #870458

    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.

  • September 11, 2008 at 10:45 am

    #870478

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

  • September 11, 2008 at 11:10 am

    #870501

    Its done

  • September 11, 2008 at 11:14 am

    #870505

    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

  • September 11, 2008 at 11:16 am

    #870510

    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.

  • September 11, 2008 at 11:17 am

    #870511

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

    Kyle

  • September 11, 2008 at 11:20 am

    #870515

    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]

  • September 11, 2008 at 11:33 am

    #870520

    Thank you Kyle,

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

  • September 11, 2008 at 11:43 am

    #870529

    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

  • September 11, 2008 at 11:43 am

    #870530

    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.

    😎

  • September 11, 2008 at 12:18 pm

    #870561

    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.

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

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