Alex P Alex P - 1 month ago 7
SQL Question

Using WHILE LOOP to insert into Junction Table

I'm using the following query to insert into a JunctionTable that is used to match 2 tables (Listing & Categories) for a sort of yellowpages directory.

Any given Listing can be attributed 1 or more categories.

The following query is inserting 3 records into my Junction table with 1 problem. I have 3 fields (ID,Junc_LID,Junc_CatID) representative of the ID columns in my two other tables LID being Listing ID and CatID being the Category ID.

Running the query is adding 3 records with 1,2,43,34 in the CatID field rather than inserting 4 times each with a different catID.

So if a user select 10 categories from my web-form. The query should loop 10 times inserting 10 rows into my JunctionTable 1 for each category the user selects. Inputting 1 CatID per insert rather than a string of all the categories comma delimited as it's doing now.

enter image description here

DECLARE @cnt INT = 0;

WHILE @cnt < 3
BEGIN
INSERT INTO BND_ListingJunction_testing (Junc_LID,Junc_CatID)
Values ('[PulledLID]','[CatID]')
SET @cnt = @cnt + 1;
END;


--------------------------------------UPDATE

Here is your query modified for my tokens. It does work but adds some additional inserts into my junction table that I have not idea where they are coming from.

DECLARE @CatIDStr VARCHAR(100) = '[CatID]',@CatID VARCHAR(100) = ''

WHILE LEN(@CatIDStr) > 0
BEGIN

IF CHARINDEX(',',@CatIDStr) = 0
BEGIN
SET @CatID = @CatIDStr
SET @CatIDStr = ''
END
ELSE
BEGIN
SELECT @CatID = SUBSTRING(@CatIDStr,0,CHARINDEX(',',@CatIDStr))
SELECT @CatIDStr=SUBSTRING(@CatIDStr,CHARINDEX(',',@CatIDStr)+1,LEN(@CatIDStr))
END

INSERT INTO BND_ListingJunction_testing (Junc_LID,Junc_CatID)
Values ('[ScopedLID]',@CatID)

END


enter image description here

Answer

Try this

  DECLARE @CatIDStr VARCHAR(100) = '1,2,43,34',@CatID VARCHAR(100) = ''

WHILE LEN(@CatIDStr) > 0
BEGIN

   IF CHARINDEX(',',@CatIDStr) = 0
   BEGIN
      SET @CatID = @CatIDStr
      SET @CatIDStr = ''
   END  
   ELSE
   BEGIN
     SELECT @CatID = SUBSTRING(@CatIDStr,0,CHARINDEX(',',@CatIDStr))                                        
     SELECT @CatIDStr=SUBSTRING(@CatIDStr,CHARINDEX(',',@CatIDStr)+1,LEN(@CatIDStr))
   END

   INSERT INTO BND_ListingJunction_testing (Junc_LID,Junc_CatID)
   Values ('[PulledLID]',@CatID) 

END