WHILE LOOP Tsql help

Question

question

Slick84 asked Feb 11, '10

WHILE LOOP Tsql help

Guys,

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

t-sql loop

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-11T13:22:59Z

Matt Whitfield answered Feb 11, '10

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...

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Feb 11, 2010 at 01:57 PM

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.

0 ·

Answer 2 · 2010-02-11T15:42:52Z

Ian Roke answered Feb 11, '10

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.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 commented · Mar 15, 2010 at 01:32 PM

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.

0 ·

question