Multiple insert statements - increment variable-CodePudding

I have a table

number | letter
------- ---------
1      | A
2      | B

And I have this code:

declare @counter as int = 0

while (@counter < 16)
begin
    set @counter = @counter   1

    insert into table (number, letter) 
    values (@counter, 'A')

    insert into table (number, letter) 
    values (@counter, 'B')
end

The problem with I have with this statement is that it is producing something like this:

number | letter
------- ----------
1      | A
1      | B
2      | A
2      | B

What I wanted is there are 8 rows since the counter stops after 15 and @counter started from 0

number | letter
------- ---------
1      | A
2      | B
3      | A
4      | B
5      | A
6      | B
7      | A
8      | B

I have tried putting BEGIN and END per statement but I still can't achieve my goal:

declare @counter as int = 0

while (@counter < 16)
begin
    insert into table (number, letter) 
    values (@counter, 'A')
end
begin
    insert into table (number, letter) 
    values (@counter, 'B')

    set @counter = @counter   1
end

CodePudding user response：

I'm with @Larnu, why not simply using IDENTITY on number?

But if you insist doing it on your own, one method would be to increment the counter by 2 instead of 1 and insert the current value along with 'A' and the current value 1 along with 'B'.

DECLARE @counter AS integer = 1;

WHILE @counter <= 8
BEGIN
  INSERT INTO elbat
              (number,
               letter)
              VALUES (@counter,
                      'A');
  INSERT INTO elbat
              (number,
               letter)
              VALUES (@counter   1,
                      'B');
  SET @counter = @counter   2;
END;

db<>fiddle