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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you give sample data that you will insert into topic and statemtent tables?
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'
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
ASKER
Right now my data looks like this.
DATABASE$
TopicID Text Topics
1 'there shold be an ongoing commitment to this' 'Academics; Health'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Thanks