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