SQL - Trying to add rows to a table with while loop, but no rows get added and I get no error messag-CodePudding

I am trying to add rows of new IP addresses to an existing table called IP Alloc, but no rows get added. I dont get errors either. What is happening?

SELECT 
    *
FROM
    IP_Alloc
BEGIN
         
    DECLARE @IPaddress VARCHAR
    SET @IPaddress = '.'

    DECLARE @IPoct1 VARCHAR
    SET @IPoct1 = 10

    DECLARE @IPoct2 VARCHAR
    SET @IPoct2 = 200

    DECLARE @IPoct3 VARCHAR
    SET @IPoct3 = 20

    DECLARE @IPoct4 INT
    SET @IPoct4 = 0
END

WHILE (@IPoct4 < 256)
BEGIN
    SET @IPaddress = CONCAT(@IPoct1,'.', @IPoct2,'.' , @IPoct3,'.', @IPoct4);

    INSERT INTO IP_Alloc (site, area, line, Device, deviceDescription, PartNo, IPaddress, deviceType, MACaddress, comment, IPoct1, IPoct2, IPoct3, IPoct4, created, hostName, modelNo, modified, revision, series, switchPort, visible, wallPlate)
--Generic values, to be changed later               
    VALUES ('BIL', 'area', 'line', 'Device', 'deviceDescription', 'PartNo', @IPaddress, 'deviceType', 'MACaddress','comment', @IPoct1, @IPoct2, @IPoct3, @IPoct4, '0000-00-00', 'hostName', 'modelNo', '0000-00-00', 'revision', 'series', 'switchPort', '0', 'wallPlate')
               
    SET @IPoct4 = @IPoct4   1 
END

CodePudding user response：

So you have some issues with your data type declarations. @IPoct4 needs to be an int as you have already to be able to iterate through the variable.

@IPaddress needs to be VARCHAR as it has a . in however you need to add the length declaration. If its not specified the length defaults to 1. VARCHAR(15) will work sufficiently for an ip address.

Then for @IPoct1,@IPoct2,@IPoct3 you're currently specifying these as varchar however you're setting them using a number without speech marks. You can only do that when your defining INTs. You're much better off defining it as an int

You also have a begin and end which isn't needed either. So your Query would turn into:

DECLARE @IPaddress VARCHAR(15)
SET @IPaddress = '.'

DECLARE @IPoct1 INT
SET @IPoct1 = 10

DECLARE @IPoct2 INT
SET @IPoct2 = 200

DECLARE @IPoct3 INT
SET @IPoct3 = 20

DECLARE @IPoct4 INT
SET @IPoct4 = 0

WHILE (@IPoct4 < 256)
BEGIN
    SET @IPaddress = CONCAT(@IPoct1,'.', @IPoct2,'.' , @IPoct3,'.', @IPoct4);

    INSERT INTO IP_Alloc (site, area, line, Device, deviceDescription, PartNo, IPaddress, deviceType, MACaddress, comment, IPoct1, IPoct2, IPoct3, IPoct4, created, hostName, modelNo, modified, revision, series, switchPort, visible, wallPlate)
--Generic values, to be changed later               
    VALUES ('BIL', 'area', 'line', 'Device', 'deviceDescription', 'PartNo', @IPaddress, 'deviceType', 'MACaddress','comment', @IPoct1, @IPoct2, @IPoct3, @IPoct4, '0000-00-00', 'hostName', 'modelNo', '0000-00-00', 'revision', 'series', 'switchPort', '0', 'wallPlate')
               
    SET @IPoct4 = @IPoct4   1 
END