SQL Server double recording in while

Question

I want to record by importing data from 2 tables with the while loop.

Table 1: ÜRÜNLER
Table 2: SABLONLAR
Table 3: AllProducts

The variations of the products in URUNLER are included in the SABLONLAR table.

I want to add product variants to the AllProducts table.

The first cycle is happening normally.

In the 2rd loop, the records are added as 2 records.
In the 3rd loop, the records are added as 3 records.
In the 4rd loop, the records are added as 4 records. ...
In the 20rd loop, the records are added as 20 records. ...

it goes ..

Where did I go wrong?

DECLARE @URUNLER TABLE (ID int, VARYASYONSABLONID int, BARKOD varchar(50), STOKKOD varchar(50), BASLIK varchar(500))

INSERT INTO @URUNLER 
    SELECT ID, VARYASYONSABLONID, BARKOD, STOKKOD, BASLIK 
    FROM URUNLER

DECLARE @ToplamUrun int = (SELECT COUNT(VARYASYONSABLONID) FROM @URUNLER)

DECLARE @SABLONLARALL TABLE ([ID] [int], [STOKKODU] [varchar](500),[INDIRIMLIFIYAT] [decimal](18, 2), [STOKFIYATI] [decimal](18, 2), [STOKFIYATI_OLD] [decimal](18, 2), [OZELLIK1] [varchar](500), [OZELLIK2] [varchar](500), [MALIYETTABLO] [varchar](50), [ISIM1] [varchar](500), [ISIM2] [varchar](500), [MIKTAR] [varchar](500), [MALIYETTUTARI] [decimal](18, 2), [PIYASAFIYATI] [decimal](18, 2), [GTIN] [varchar](500), [MPN] [varchar](500),[BUNDLE] [bit], [SABLONID] [int], [TABLOADET] [int])

INSERT INTO @SABLONLARALL 
    SELECT
        [ID], [STOKKODU], [INDIRIMLIFIYAT], [STOKFIYATI], [STOKFIYATI_OLD],
        [OZELLIK1], [OZELLIK2], [MALIYETTABLO], [ISIM1], [ISIM2],
        [MIKTAR], [MALIYETTUTARI], [PIYASAFIYATI], [GTIN], [MPN],
        [BUNDLE] [bit], [SABLONID], [TABLOADET] 
    FROM
        SABLONLAR

DECLARE @WhileCount int = 0;
DECLARE @While2Count int;
DECLARE @SABLONCOUNT int;

WHILE @WhileCount <= @ToplamUrun
Begin

    SET @While2Count = 0;

    declare @ID int;
    declare @VARYASYONSABLONID int;
    declare @BARKOD varchar(50);
    declare @STOKKOD varchar(50);
    declare @BASLIK varchar(500);
    SELECT @ID = ID, @VARYASYONSABLONID = VARYASYONSABLONID, @BARKOD = BARKOD, @STOKKOD = STOKKOD, @BASLIK = BASLIK from @URUNLER ORDER BY ID OFFSET @WhileCount ROWS FETCH NEXT 1 ROWS ONLY
    
    declare @SABLON Table ([ID] [int],[STOKKODU] [varchar](500),[INDIRIMLIFIYAT] [decimal](18, 2),[STOKFIYATI] [decimal](18, 2),[STOKFIYATI_OLD] [decimal](18, 2),[OZELLIK1] [varchar](500),[OZELLIK2] [varchar](500),[MALIYETTABLO] [varchar](50),[ISIM1] [varchar](500),[ISIM2] [varchar](500),[MIKTAR] [varchar](500),[MALIYETTUTARI] [decimal](18, 2),[PIYASAFIYATI] [decimal](18, 2),[GTIN] [varchar](500),[MPN] [varchar](500),[BUNDLE] [bit],[SABLONID] [int],[TABLOADET] [int])
    Insert into @SABLON select [ID],[STOKKODU],[INDIRIMLIFIYAT],[STOKFIYATI],[STOKFIYATI_OLD],[OZELLIK1],[OZELLIK2],[MALIYETTABLO],[ISIM1],[ISIM2],[MIKTAR],[MALIYETTUTARI],[PIYASAFIYATI],[GTIN],[MPN],[BUNDLE] [bit],[SABLONID],[TABLOADET] from SABLONLAR WHERE SABLONID = @VARYASYONSABLONID

    SET @SABLONCOUNT = (select Count(STOKKODU) from @SABLON)

    While @While2Count <= @SABLONCOUNT
    BEGIN

        declare @IDx int;
        declare @PIYASAFIYATI decimal(18,5);
        declare @STOKFIYATI decimal(18,5);
        declare @OZELLIK1 varchar(255);
        declare @OZELLIK2 varchar(255);
        declare @VSTOKKOD varchar(255);
        SELECT @IDx = ID, @PIYASAFIYATI = PIYASAFIYATI, @STOKFIYATI = STOKFIYATI, @OZELLIK1 = OZELLIK1, @OZELLIK2 = OZELLIK2, @VSTOKKOD = STOKKODU from @SABLON ORDER BY ID OFFSET @While2Count ROWS FETCH NEXT 1 ROWS ONLY

        IF @PIYASAFIYATI is null
        BEGIN
            SET @PIYASAFIYATI = (@STOKFIYATI / 100) * 140;
        END
        declare @KALAN decimal(18,5) = @PIYASAFIYATI % 5;
        if @KALAN > 0
        BEGIN
            SET @PIYASAFIYATI = @PIYASAFIYATI + (5 - @KALAN)
        END
        declare @KALAN2 decimal(18,5) = @STOKFIYATI % 5;
        if @KALAN2 > 0
        BEGIN
            SET @STOKFIYATI = @STOKFIYATI + (5 - @KALAN2)
        END

        select '@WhileCount' = @WhileCount,'@While2Count' = @While2Count

        INSERT INTO [dbo].[ALLProducts]
           ([Baslik]
           ,[Barkod]
           ,[StokKodu]
           ,[PiyasaFiyati]
           ,[SatisFiyati])
        VALUES
           (@BASLIK + ' ' + @STOKKOD + ' ' + REPLACE(@OZELLIK1, 'Tabloların 3''ü', '3 Tablo') + (CASE WHEN @OZELLIK2 is not null THEN ' ' + @OZELLIK2 ELSE '' END)
           ,@BARKOD + REPLACE(@VSTOKKOD, '-', '')
           ,@STOKKOD + @VSTOKKOD
           ,@PIYASAFIYATI
           ,@STOKFIYATI)

        Set @While2Count +=1;
    END

    Set @WhileCount +=1;
END

Must be

Formed

EDİT::

The URUNLER table contains the main information of the product.

In the SABLONLAR table, the variant values of the product take place (size, shoe size, table size types).

I will also make an AllProducts table. Each variant option will appear as a single product in this table.

A simple example;

This is the product in the URUNLER table;

This is the template in the SABLONLAR table;

Now let's save the products individually in the AllProducts table.

The *real* question is why are you using a `WHILE` in the first place. SQL is a set based language and you should be using set-based solutions. What are you actually trying to achieve here? — Thom A, Oct 15 '20 at 15:42
So, `insert into all_products select * from URUNLER inner join SABLONLAR on URUNLER.STOKKOD = SABLONLAR.STOKKODU`?.. — GSerg, Oct 17 '20 at 12:13
Listen to @Larnu and forget about using a WHILE loop for this purpose. [This](https://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232) and [this](https://www.sqlservercentral.com/articles/the-numbers-or-tally-table-what-it-is-and-how-it-replaces-a-loop-1) could provide a useful alternative — SteveC, Oct 17 '20 at 13:40

SQL Server double recording in while

0 Answers0