WHILE LOOP Tsql help


I have a while loop I run to loop through an insert statement. I insert records as long as the integer value inside one of the columns (COMPONENT_QTY) is greater than > 1.

Problem im having is that im using a left join to insert only records into the table whcih dont already exist. Now when I loop through the first time, it inserts the first record, but it doesnt insert the second (3rd, 4th, 5th, 6th, so on....) time because it detects the left join and says oh this record already exists so no more insert.

How do I work around this? TSQL Below:

DECLARE @i AS INT SET @i = 1 WHILE @i <= (SELECT MAX(COMPONENT_QTY) FROM [DB].[dbo].[vwViewIndex])  BEGIN  INSERT INTO [DB1].[dbo].[VariableKitComp]  (  Id_Kit,  Id_Component  )  SELECT  pr1.Id,  pr2.Id  FROM [DB].[dbo].[vwViewIndex] ki  INNER JOIN [DB1].[dbo].[Product] pr1 ON  ki.STYCD = pr1.ShrtSKU  INNER JOIN [DB1].[dbo].[Product] pr2 ON  ki.COMP_STYCD = pr2.ShrtSKU  LEFT JOIN [DB1].[dbo].[VariableKitComp] pr3 ON  pr3.Id_Kit = pr1.Id  WHERE ki.IsArchive = 0 AND ki.COMPONENT_QTY >= @i AND pr3.Id_Kit IS NULL  SET @i = @i + 1  END 
more ▼

asked Feb 11, 2010 at 01:10 PM in Default

Slick84 gravatar image

1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It seems to me that you either do want to insert records that already exist, or you don't. Decide which is which, then remove / keep the AND pr3.Id_Kit IS NULL as appropriate.

However, have you considered doing this with a Tally table rather than a while loop? It's a bit difficult to see the logic going on, but you could join to a tally table where the number was between 1 and (SELECT MAX(COMPONENT_QTY) FROM [DB].[dbo].[vwViewIndex]), and then insert all the records in one pass.

And that would presumably make the problem you're asking come out in the wash...

more ▼

answered Feb 11, 2010 at 01:22 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

You are right, it does seem like I want to both insert existing and not at the same time. Its kind of confusing. The way the table is designed is very wierd. Anyhow, I think I might have a fix using temporary tables. I will post the solution here once I have it test. Thanks for your input.
Feb 11, 2010 at 01:57 PM Slick84
(comments are locked)
10|1200 characters needed characters left

I'm struggling to picture the database structure in my head. Would it be possible to provide a structure and then I could help you more.

more ▼

answered Feb 11, 2010 at 03:42 PM

Ian Roke gravatar image

Ian Roke
1.7k 30 33 34

I have the solution. I was able to use a temporary table to isolate all non-existing records first. Then just use the above while loop to insert all of them in there using an inner join. Thanks for your help everyone.
Mar 15, 2010 at 01:32 PM Slick84
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 11, 2010 at 01:10 PM

Seen: 6414 times

Last Updated: Feb 11, 2010 at 01:10 PM