Link to home
Start Free TrialLog in
Avatar of kpbarem
kpbarem

asked on

how to switch names to numbers for insert statement

This was a difficult question for me to figure out how to word, but the problem is that I have three tables set up like this.

Statement (TopicID int identity(1,1), text varchar(500), modifiedDate datetime)
Topic( Topic_Code int identity (10,10), topicName varchar(150))
Statement_Topics (TopicID, TopicCode)

Statement_Topics is the go between table between topic and Statement.  I was given a list of data in excel format that contains all of the Statements and there corresponding topics seperated by a ; in this format.
Topic_ID  Text                                Topic(s)
#              blah blah blah               topic1; topic2;
#              blah blah blah               topic5; topic6
 
However, I am unsure how to get all of this information into my tables correctly.

I was able to load the excel data into a table called database$ and from there I was able to get all of the seperate topics into the topic table.  At this point I need to basically say if the topicID corresponds to topic1 and topic 2 then put topic1;topic2 in the database.

I need to fill out the Statement_Topics table and i'm not sure how to do this.  Can anyone help me out with some ideas?
SOLUTION
Avatar of Carlo-Giuliani
Carlo-Giuliani
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Can you give sample data that you will insert into topic and statemtent tables?
Avatar of kpbarem
kpbarem

ASKER

STATEMENT
TopicID      Text                                                                               ModifiedDate
1                 'there should be an ongoing commitment to this'   01/20/2012

STATEMENT_TOPICS
TopicID         TopicCodes
1                        10,20


CODE_TOPICS
TopicCode      TopicName
10                     'Academics'
20                     'Health'

Open in new window

Sure! I could give you an example if it helps the understanding. I can't give a lot of sample data because its under proprietary rules. But for example here is how I want the final results to look
Avatar of kpbarem

ASKER

Right now my data looks like this.

DATABASE$
TopicID          Text                                                                                       Topics
1                     'there shold be an ongoing commitment to this'             'Academics; Health'

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I see you've created almost a normalized structure with three tables:
Statement (TopicID int identity(1,1), text varchar(500), modifiedDate datetime)
Topic( Topic_Code int identity (10,10), topicName varchar(150))
Statement_Topics (TopicID, TopicCode)

The only normalization problem I see is storing " topic1; topic2;" (and 10, 20) in one field.
You'll need to split these in individual topics / records like the sample of Carlo-Giuliani shows and use those for filling the tables.

I would start with (temporary) adding the "Topic(s)" column to the Statement table.
Next fill the Statement table from the database$ table, getting the autonumber available.

Next use a split (check e.g. split string) to get for each separate topic a select query and use a UNION to concatenate these queries into one.
Now we can use an INSERT with a DISTINCT to fill the table Topics with one row for each unique topic.
Finally you can JOIN the "UNION" query with the Topics table by the TopicName to get the TopicID and TopicCode to fill the relation table Statement_Topics
Avatar of kpbarem

ASKER

Thanks