how to switch names to numbers for insert statement

Posted on 2012-08-14
Last Modified: 2012-08-15
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?
Question by:kpbarem
    LVL 12

    Assisted Solution

    First of all, I think the first column in that sample data is supposed to be statement ids, not topic ids.  Otherwise it makes no sense at all.

    Your problem is the variable number of value in each row.  You neeed to rearrange your data so it looks like this:

    Statement_ID        Text                        Topic                
    1                   blah blah blah               topic1
    1                   blah blah blah               topic2
    2                   blah blah blah               topic5
    2                   blah blah blah               topic6

    Open in new window

    Do that using whatever scripting tool you are most comfortable with.

    Once you have done that I think it is pretty obvious how to proceeed.
    LVL 25

    Expert Comment

    Can you give sample data that you will insert into topic and statemtent tables?

    Author Comment

    TopicID      Text                                                                               ModifiedDate
    1                 'there should be an ongoing commitment to this'   01/20/2012
    TopicID         TopicCodes
    1                        10,20
    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

    Author Comment

    Right now my data looks like this.

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

    Open in new window

    LVL 41

    Accepted Solution

    This is one way of doing this

    1) Create this function

    CREATE FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) ) 
    returns @result TABLE (Value varchar(100)) 
        declare @dx varchar(9) 
        -- declare @loops int 
         --set @loops = 0 
         DECLARE @TempList table 
              Value varchar(100) 
         if @delimiter is null  set @delimiter = ' ' 
         if len(@delimiter) < 1 set @delimiter = ' ' 
         set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1) 
         DECLARE @Value varchar(8000), @Pos int 
         SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter 
         SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 
         IF REPLACE(@Parameters, @delimiter, @dx) <> '' 
              WHILE @Pos > 0 -- AND @Loops < 100 
                   --set @loops = @loops + 1 
                   SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1))) 
                   IF @Value <> '' 
                        INSERT INTO @TempList (Value) VALUES (CAST(@Value AS varchar)) --Use Appropriate conversion 
                   SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000) 
                   SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 
         INSERT @result 
         SELECT value 
            FROM @TempList 

    Open in new window

    2) Then to populate the code_topics table do:

    insert code_topics
    select distinct b.Value
    from database$ a
    crossapply dbo.parmstolist(a.Topics, ';') b

    Open in new window

    3) To populate the statement_topics do

    ;with cte as(
    	select t1.TopicID, t2.TopicCode
    	from (
    		select a.TopicID, b.Value
    		from database$ a
    		crossapply dbo.parmstolist(a.Topics, ';') b
    	) t1
    	inner join code_topics t2 on t1.Value = t2.TopicName
    insert statement_topics
    select TopicID, stuff(select ', ' + TopicCode from cte where TopicID = a.TopicID order by 1 for xml path('')), 1, 1, '')
    from cte a

    Open in new window

    LVL 5

    Expert Comment

    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

    Author Closing Comment


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now