CODEWITHSUNDEEP
sqldatabaset-sql
joncodo
joncodo

Reputation: 2338

Why is this sql line not working

It says that the command executed successfully but there are no values inserted into the organization table.

DECLARE @now DATETIME
SET @now = GETDATE()
DECLARE @numtoinsert INT
SET @numtoinsert = 100

DECLARE @counter INT
SET @counter = 101
WHILE @counter < @numtoinsert
BEGIN
SET @counter = @counter + 1

INSERT INTO [MVServices].[dbo].[Organization]
       ([Organization_Id]
       ,[Business_Number]
       ,[Legal_Name]
       ,[Common_Name]
       ,[Operating_As]
       ,[Sort_Name]
       ,[Effective_Date]
       ,[Expiry_Date]
       ,[Created_By]
       ,[Created_Date]
       ,[Last_Changed_By]
       ,[Update_Date])
 VALUES
       (@counter
       ,1234
       ,'ABC Construction'
       ,'ABC'
       ,'ABC Construction'
       ,'ABC Construction'
       ,@now
       ,null
       ,'seed'
       ,@now
       ,null
       ,null)

       END

Upvotes: 3

Views: 118

Answers (4)

Chris McLaren
Chris McLaren

Reputation: 21

You have two variables as follows:

SET @numtoinsert = 100
SET @counter = 101

The insert statement is then within a while loop:

WHILE @counter < @numtoinsert

counter is not greater than numtoinsert to begin with so the insert statement is not executed.

Upvotes: 2

Darren
Darren

Reputation: 70748

Your logic is incorrect:

SET @numtoinsert = 100
SET @counter = 101

WHILE @counter < @numtoinsert

You are saying:

WHILE 101 IS SMALLER THAN 100

This evaluates to false, so you never enter the while loop.

Upvotes: 1

Guffa
Guffa

Reputation: 700562

That's because you insert records as long as @counter < @numtoinsert, and as @counter is 101, it's never smaller than @numtoinsert which is 100.

Upvotes: 3

Widor
Widor

Reputation: 13275

It doesn't insert anything because your WHILE condition is:

WHILE @counter < @numtoinsert

And 101 > 100, so it never enters the loop.

Upvotes: 9

Related Questions