Post reply

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

  • September 11, 2008 at 1:39 pm

    #870608

    [Quote]

    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.[/Quote]

    My question was entirely of syntax, I was aksing for the correct syntax for a particular function. I was not asking for someone to complete an entire project for me.

    If I had tried to work out that syntax myself the only thing I would have learnt is that it takes too long and its much better to ask on an online forum, as I have learnt many times in the last 8 years I have spent computer programming. You can post a question on a forum about syntax, then spend a whole day trying to work it out and then, having finally worked it out, you see that someone has posted the answer right there.

    If I was doing this as "homework", I would have asked the teacher for the syntax.

  • September 11, 2008 at 1:46 pm

    #870614

    No offense, but that's just the way it felt to me... and we get that all the time here.

    Next time you might want to check the Books Online (BOL for short). You have all the syntaxe you need there :w00t:.

  • September 15, 2008 at 3:22 pm

    #872048

    [Quote]

    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

    [/Quote]

    Yes, this is now where I am stuck, I can create the indices and foreign keys in Enterprise Manager. But how can I replace the city name with cityid?

  • September 15, 2008 at 3:51 pm

    #872063

    OK I did a work around, I created a new primary key in the cities table and then joined this to the base table (outer join), created a new table out of that join (using the "select into" command again) and then discarded the old base table, using just the new joined table as the base table.

    There is probably a simpler way, but this worked for my purpose.

  • September 15, 2008 at 8:42 pm

    #872105

    Yup :

    Alter Table

    Add FkColumn

    Alter Table

    Add Fk Constraint

    Update Table SET CityID = Cities.CityID FROM Table Inner join Cities ON Table.City = Cites.City

    ALTER TABLE

    DROP COLUMN City --you must drop any associated objects of the column first (default, check, FK, indexes, schema bound views)

    Make sure you transfered all the objects from the old to the new table (constraints, PKs, FKS, defaults, indexes, etc).

Viewing 5 posts - 16 through 19 (of 19 total)

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