CODEWITHSUNDEEP
sqlsql-server-2008
levi Clouser
levi Clouser

Reputation: 358

SQL Server insert loop of strings

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.

Upvotes: 0

Views: 141

Answers (2)

levi Clouser
levi Clouser

Reputation: 358

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

Upvotes: 1

Mahesh Malpani
Mahesh Malpani

Reputation: 1989

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

Upvotes: 2

Related Questions