x

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
more ▼

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

avatar image

Slick84
1.3k 75 104 147

(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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

avatar image

Ian Roke
1.7k 32 35 38

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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1069
x28

asked: Feb 11, 2010 at 01:10 PM

Seen: 6698 times

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

Copyright 2016 Redgate Software. Privacy Policy