May 23, 2006 at 3:45 pm
My quandry uses 3 tables. The first table, OLD_TABLE, has position_name. For the NEW_TABLE I want to store position_id instead. I have a third table that has position_name and position_id. I want to insert the appropriate position_id in NEW_TABLE where they match the position_name in the OLD_TABLE for each person. How can this be done in SQL?
OLD_TABLE
name
position_name
NEW_TABLE
name
position_id
POSITIONS_TABLE
position_id
position_name
Thank you very much!!!
May 23, 2006 at 3:50 pm
Where will you get the data for the Position_ID column? Will Position_ID in your New_Table be an IDENTITY column?
May 23, 2006 at 3:54 pm
position_id is an identity column in the POSITIONS_TABLE. It will not be an identity column in NEW_TABLE.
Basically, I'm wanting to store IDs instead of names.
May 23, 2006 at 4:08 pm
It sounds to me like you are wanting a way to populate NEW_TABLE and POSITIONS_TABLE from what you have in OLD_TABLE. If this is true, and from what you've shown in your post, here's how to use SQL to do this (keep in mind that what I've come up with is based on my understanding of your table structure and the relationships between your tables. All of this was inferfed from your post. If the CREATE TABLE statements do not match up with your table design, please post your table DDL so that I can tune the SQL shown below. If my DDL matches your table, my INSERT statements should get you what you are looking for):
CREATE TABLE OLD_TABLE ([name] varchar(50), position_name varchar(50))
CREATE TABLE NEW_TABLE ([name] varchar(50, position_id int)
CREATE TABLE POSITIONS_TABLE (position_id int identity(1,1), postion_name varchar(50)
INSERT INTO POSITIONS_TABLE (Position_name)
SELECT Position_name
FROM OLD_TABLE
INSERT INTO NEW_TABLE ([name], position_id)
SELECT ot.[name], pt.Position_id
FROM OLD_TABLE ot
INNER JOIN POSITIONS_TABLE pt
ON ot.Position_Name = pt.Position_Name
May 23, 2006 at 5:21 pm
Thank you Mr. Rowan!
May 23, 2006 at 7:38 pm
You will need to be sure that you can treat position_name in the positions_table as unique.
The least stringent check you can make, in case it's of any use:
from old_table ot
from positions_table pt
)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
May 23, 2006 at 7:40 pm
from positions_table pt
from old_table ot)
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply