[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 697
  • Last Modified:

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?
2 Solutions
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.
Can you give sample data that you will insert into topic and statemtent tables?
kpbaremAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

kpbaremAuthor Commented:
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

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

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
kpbaremAuthor Commented:

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now