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

Viewing 4 posts - 16 through 20 (of 20 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

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 2

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

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 4

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