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

Question

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

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

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

garethmann101 SSCommitted Points: 1980 More actions · Answer 1

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

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

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

garethmann101 SSCommitted Points: 1980 More actions · Answer 3

[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?

garethmann101 SSCommitted Points: 1980 More actions · Answer 4

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.

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

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