Create a number of new rows in SQL Server DB with null values

Question

I would like to create a set of new rows in a DB where ID is = to 10,11,12,13,14,15 but all other values are null. This is assuming rows 1 through 9 already exist (in this example). My application will set the first and last row parameters.

Here's my query to create one row but I need a way to loop through rows 10 through 15 until all five rows are created:

        @FirstRow int = 10 --will be set by application
        ,@LastRow int = 15 --will be set by application
        ,@FileName varchar(100) = NULL
        ,@CreatedDate date = NULL
        ,@CreatedBy varchar (50) = NULL   

AS
BEGIN
        INSERT INTO TABLE(TABLE_ID, FILENAME, CREATED_BY, CREATED_DATE)
        VALUES (@FirstRow, @FileName, @CreatedBy, @CreatedDate)
END

The reason I need blank rows is because the application needs to update an existing row in a table. My application will be uploading thousands of documents to rows in a table based on file ID. The application requires that the rows already be inserted. The files are inserted after rows are added. The app then deletes all rows that are null.

You need a [numbers table](https://stackoverflow.com/questions/1393951/what-is-the-best-way-to-create-and-populate-a-numbers-table). With that you simply select the rows between the lower and upper boundaries. But I have to question why you need "blank" rows in the first place - a much bigger (and different) discussion — SMor, Apr 25 '19 at 18:31

Cowthulhu · Accepted Answer · 2019-04-25T18:38:51.980

Assuming the rows you're inserting are always consecutive, you can use a ghetto FOR loop like the one below to accomplish your goal:

--put all the other variable assignments above this line
DECLARE @i int = @FirstRow

WHILE (@i <= @LastRow)
BEGIN
    INSERT INTO TABLE(TABLE_ID, FILENAME, CREATED_BY, CREATED_DATE)
    VALUES (@i, @FileName, @CreatedBy, @CreatedDate)
    SET @i = @i + 1;
END

Basically, we assigned @i to the lowest index, and then just iterate through, one by one, until we're at the max index.

If performance is a concern, the above approach will not be ideal.

score 1 · Answer 2 · answered Apr 25 '19 at 18:45

If you don't have a numbers table as SMor mentioned, you can use an ad-hoc tally table

Example

Declare @FirstRow int = 10 --will be set by application
       ,@LastRow int = 15 --will be set by application
       ,@FileName varchar(100) = NULL
       ,@CreatedDate date = NULL
       ,@CreatedBy varchar (50) = NULL   


INSERT INTO TABLE(TABLE_ID, FILENAME, CREATED_BY, CREATED_DATE)
Select Top (@LastRow-@FirstRow+1) 
        @FirstRow-1+Row_Number() Over (Order By (Select NULL)) 
       ,@FileName
       ,@CreatedBy
       ,@CreatedDate
 From   master..spt_values n1, master..spt_values n2

Data Generated

This answer, and others like it always get my upvote! Set operations for the win! Creative use of big master tables. — Chris Steele, Apr 25 '19 at 21:05

Create a number of new rows in SQL Server DB with null values

2 Answers2