CODEWITHSUNDEEP
sqlsql-server
stianzz
stianzz

Reputation: 49

Insert into table using a select statement and a while loop

I need to insert values into a table from another table. I also want to use a while loop to update a row in my table at the same time. Below you can see my query.

   declare @id int 
select @id = 1
while @id >=1 and @id <= 3
begin
INSERT  INTO [dbo].[TEST]
([ID_PRODUCT],[PRODUCTID],[PRODUCTDESC],[COUNT]

select distinct
ID_PRODUCT,PRODUCTID,PRODUCTDESC,@id
from SAMPLES

 select @id = @id + 1
end

This works but not as i was expecting. Instead of giving me three rows affected, it gives me three rows affected, three times. So i end up with nine new rows instead of the desired three i want. 

ID_PRODUCT PRODUCTID  PRODUCTDESC COUNT
35746      136559     Desc1       1
35747      276732     Desc2       1
35748      259910     Desc3       1
35746      136559     Desc1       2
35747      276732     Desc2       2
35748      259910     Desc3       2
35746      136559     Desc1       3
35747      276732     Desc2       3
35748      259910     Desc3       3

What i want to acheive is this :

ID_PRODUCT PRODUCTID  PRODUCTDESC COUNT
    35746      136559     Desc1       1
    35747      276732     Desc2       2
    35748      259910     Desc3       3

Can anyone see what im doing wrong?

Upvotes: 0

Views: 8254

Answers (4)

Pratik Patel
Pratik Patel

Reputation: 78

Eralper and Ivan Starostin both answered correctly and it is right solution for you.

To run your code correctly, you need to add WHERE clause.

declare @id int 
select @id = 1
while @id >=1 and @id <= 3
begin
INSERT  INTO [dbo].[TEST]
([ID_PRODUCT],[PRODUCTID],[PRODUCTDESC],[COUNT]
select distinct
ID_PRODUCT,PRODUCTID,PRODUCTDESC,@id
from 
SAMPLES s
WHERE Count = @id
AND NOT EXISTS(SELECT 1 FROM Test t WHERE t.ID_PRODUCT = s.ID_PRODUCT 
                                      AND t.PRODUCTID = s.PRODUCTID)
select @id = @id + 1
end

Upvotes: 0

neer
neer

Reputation: 4082

You are close. Add top 1 and where clause like ID_PRODUCT not in Test table

declare @id int 
select @id = 1
while @id >=1 and @id <= 3
begin
INSERT  INTO [dbo].[TEST]
([ID_PRODUCT],[PRODUCTID],[PRODUCTDESC],[COUNT]

SELECT DISTINCT TOP 1
    ID_PRODUCT,
    PRODUCTID,
    PRODUCTDESC,
    @id
from 
    SAMPLES S
WHERE
    S.ID_PRODUCT NOT IN
    (
        SELECT T.ID_PRODUCT FROM [TEST] T
    )

 select @id = @id + 1
end

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

You are inserting three times same list of rows with different @id.

I guess you actually mean this:

;with DistinctSampleValues as
(
    select distinct
        ID_PRODUCT,PRODUCTID,PRODUCTDESC
    from SAMPLES
)
insert into [dbo].[TEST] ([ID_PRODUCT],[PRODUCTID],[PRODUCTDESC],[COUNT])
select
    ID_PRODUCT,PRODUCTID,PRODUCTDESC,
    ROW_NUMBER() OVER(ORDER BY ID_PRODUCT) RN
from DistinctSampleValues

one time insert all distinct values with additional "row number".

Upvotes: 0

Eralper
Eralper

Reputation: 6612

The SELECT part of Insert statement always returns same records @id does not change the selected rows, only inserts same data set with different @id values

select distinct
ID_PRODUCT,PRODUCTID,PRODUCTDESC,@id
from SAMPLES

Upvotes: 1

Related Questions