Help parsing & insert while reading table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help parsing & insert while reading table variable

Author

Topic

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 10:57:03

Knowing that my @Tab table variable returns data for example like this:

ProductID (bigint) ChildIDs (varchar string of comma delimited IDs)
------------- ------------------------------------------------------
106731 105234,105235,105236,105237,105238
107889 101238, 422635,167236,105237,105974, 230554
108990 301234, 425235, 344422
…and so on

I need help in coding the following:

1) Cycle through my @Tab results like above and insert into my ProductRelationship Table an entry for each Parent/Child. So for example for record 106731 I should have 5 inserts like this into my ProductRelationship table:

ProductID RelatedProductID
------------- ------------------------
106731 105234
106731 105235
106731 105236
106731 105237
106731 105238
107889 101238
107889 422635
107889 167236
… and so on

ProductRelationship
----------------------------
ProductID (bigint)
RelatedProductID (bigint)

I have no idea how to take the results from my @Tab and do this in SQL. The ChildIDs are a comma delimited string of IDs and not every ProductID has the same number of ChildIDs as you can see.

So far I only have this as a starting point, I just need to know how to do this split/instert for each product in my @Tab:

WHILE @Rows > 0
BEGIN
SELECT ProductID,
ChildProductIDs,
FROM @tab

WHERE i = @Rows

-- Read @Tab Row and then INSERT each Product/Child as new record in ProductRelationships table

SET @Rows = @Rows -1
END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-10 : 11:00:25

Check this link for getting some of the best split functions:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 11:10:55

Thanks, but remember, my ChildIDs is not a fixed string...it varies in length

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-10 : 11:24:18

Is your data stored in a CSV lists like that, or are you putting it in that format when populating your table variable? You are much, much better off keeping your data normalized!

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 11:35:39

Did you even READ the topic provided to you?

Peter Larsson
Helsingborg, Sweden

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 14:12:35

Ok, I figured out a function and got it working and all by modifying a function from this article: http://www.sommarskog.se/arrays-in-sql.html#iterative

I ended up using the List-of-integers Function and tweaked it to do my Inserts after passing the ChildIDs string to this function. I took out the insert they have, took out some params, put in my own params and my own insert statements and now I'm good to go.

thanks!

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 14:14:12

>>>You are much, much better off keeping your data normalized!
The entire point of this is to normalize our data. I wasn't the one who designed the shitty Product table. If it were me, we would have never had to do any of this BS. I'm actually a C# Developer, I didn't design the damn tables, our DBA and Manager did.

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 14:15:35

So in other words, not all of us design shitty schemas, and actually some of us hate having to do this because we know whoever designed the schema in the first place was stupid and this all happened before I got here.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-10 : 14:42:56

Got ya ... well, at least you are normalizing the data, that's good. Hopefully you can do a one-time conversion of this CSV crap into normalized table structures and be good to go from there. Might seem like a lot of work to change the table structure of your DB, but the benefits of better performance, easier maintenance and data integrity (not possible with CSV data) will be worth it. Good luck convincing the powers that be that a change would be beneficial, that's not always easy!

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 16:06:14

[code]DECLARE @CurrentProductID BIGINT,
@MaxProductID BIGINT,
@ChildIDs VARCHAR(8000)

SELECT @CurrentProductID = MIN(ProductID),
@MaxProductID = MAX(ProductID)
FROM @Tab

WHILE @CurrentProductID <= @MaxProductID
BEGIN
SELECT @ChildIDs = REPLACE(ChildIDs, ' ', '')
FROM @Tab
WHERE ProductID = @CurrentProductID

INSERT ProductRelationship
(
ProductID,
RelatedProductID
)
SELECT @CurrentProductID,
Data
FROM dbo.fnParseList(',', @Data)

SELECT @CurrentProductID = MIN(ProductID)
FROM @Tab
WHERE ProductID > @CurrentProductID
END[/code]
Using this function below[code]
CREATE FUNCTION dbo.fnParseList
(
@Delimiter CHAR,
@Text VARCHAR(8000)
)
RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE @NextPos SMALLINT,
@LastPos SMALLINT

SELECT @NextPos = 0

WHILE @NextPos <= DATALENGTH(@Text)
BEGIN
SELECT @LastPos = @NextPos,
@NextPos = CASE
WHEN CHARINDEX(@Delimiter, @Text, @LastPos + 1) = 0 THEN DATALENGTH(@Text) + 1
ELSE CHARINDEX(@Delimiter, @Text, @LastPos + 1)
END

INSERT @Result
(
Data
)
SELECT SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)
END

RETURN
END[/code]

Peter Larsson
Helsingborg, Sweden

dba123
Yak Posting Veteran

90 Posts

Posted - 2007-01-10 : 17:26:52

Thanks all!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources