sql loop through a list and insert records into column-CodePudding

I want to loop through a list and insert each item into a column and iterate 1000 time. I am SQL noob - can anyone help me with this?

What I have so far:

DECLARE @Counter INT 
DECLARE @myList varchar(100)

SET @Counter = 0
SET @myList = 'temp,humidity,dewpoint'

WHILE (@Counter <= 1000)
BEGIN
    INSERT INTO [DBO].[tbl_var] (VariableNames)
    VALUES (@myList)

    SET @Counter  = @Counter    1
END

I get this error:

Cannot insert the value NULL into column 'VariableNames', table 'master.DBO.tbl_var'; column does not allow nulls. INSERT fails.

What I expected

VariableNames column

1. temp
2. humidity
3. dewpoint
4. temp
5. humidity
6. dewpoint

and so on until 1000 iterations of list is complete

CodePudding user response：

If you want to INSERT each value 1000 times then use a VALUES table construct and CROSS JOIN to a tally containing 1,000 rows. I use an inline tally in the solution below:

USE YourUserDatabase;
GO

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3) --1,000 rows
INSERT INTO dbo.tbl_var (VariableNames)
SELECT V.VariableName
FROM (VALUES('temp'),
            ('humidity'),
            ('dewpoint'),
            ('temp'),
            ('humidity'),
            ('dewpoint'))V(VariableName)
     CROSS JOIN Tally T;

CodePudding user response：

I have not tested but this should work. You can use STRING_SPLIT function https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16

   DECLARE @Counter INT =1
    DECLARE @myList varchar(100)    
    SET @Counter=1
    SET @myList = 'temp,humidity,dewpoint'
    CREATE TABLE #TEMP (value varchar(255),cardinal Int)
    INSERT INTO #TEMP(value,cardinal)
    SELECT * FROM STRING_SPLIT(@myList, ',',1);

WHILE ( @Counter <= 1000)
BEGIN
    INSERT INTO  [DBO].[tbl_var] (VariableNames)
    SELECT value from #TEMP WHERE cardinal =@counter
    SET @Counter  = @Counter    1
END