SQL Server insert loop of strings

Question

Thank you in advance for your help here.

I want to insert incremental numbers as strings to load some bulk test numbers into a db, here is what i'm using:

Declare 
     @Serialcounter bigint 

set @Serialcounter = 0 

while @Serialcounter < 10 
    insert into [TrackTrace].[dbo].[TAB_ELEMENT] ([Serial], [Batch], [Batch_Id], [QCSample], [StationID]) 
    values(Convert(varchar(60), @Serialcounter), 'test', 8989, 0, 1) 

    set @Serialcounter = (@Serialcounter + 1)

but when I do this it does not increment the counter and I just insert duplicate numbers and do not stop. I think my problem is that my variable is incremented outside of the while loop, but I am not sure how to rectify this.

Hi @levi-clouser, where is the begin and the end?

Andy K
– Andy K

2016-01-21 06:26:49 +00:00
Commented Jan 21, 2016 at 6:26 — Andy K
– Andy K, Commented Jan 21, 2016 at 6:26
thank you, i knew i was missing something basic

levi Clouser
– levi Clouser

2016-01-21 06:29:08 +00:00
Commented Jan 21, 2016 at 6:29 — levi Clouser
– levi Clouser, Commented Jan 21, 2016 at 6:29

Mahesh Malpani · Accepted Answer · 2016-01-21 06:30:29Z

2

Declare 
@Serialcounter bigint 
set @Serialcounter = 0 
while @Serialcounter < 10 
BEGIN
  PRINT @Serialcounter
    --insert into [TrackTrace].[dbo].[TAB_ELEMENT] 
    --([Serial] 
    --,[Batch] 
    --,[Batch_Id] 
    --,[QCSample] 
    --,[StationID]) 
    --Values(Convert(varchar(60),@Serialcounter),'test',8989,0,1) 
    set @Serialcounter = (@Serialcounter +1 )
END

You not giving begin and end so as for all loops only first statement is considered

answered Jan 21, 2016 at 6:30

Mahesh Malpani

2,04220 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andy K · Accepted Answer · 2016-01-21 06:58:32Z

1

I was missing BEGIN and END statements

DECLARE 
@Serialcounter BIGINT 
SET @Serialcounter = 0 
WHILE @Serialcounter < 10 
BEGIN -- here
    INSERT INTO [TrackTrace].[dbo].[TAB_ELEMENT] 
    ([Serial] 
    ,[Batch] 
    ,[Batch_Id] 
    ,[QCSample] 
    ,[StationID]) 
    VALUES(Convert(varchar(60),@Serialcounter),'test',8989,0,1) 
    SET @Serialcounter = (@Serialcounter +1 )
END    -- and here

edited Jan 21, 2016 at 6:58

Andy K

5,10511 gold badges60 silver badges92 bronze badges

answered Jan 21, 2016 at 6:30

levi Clouser

3581 gold badge4 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Server insert loop of strings

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related