How to insert multiple rows into a table based on a range of numbers

Question

I need to insert a certain number of rows into a SQL Server table.

DECLARE @val AS INT = 20, @val2 AS VARCHAR(50), @Date AS DATETIME = CONVERT(DATETIME,'02-05-2016'), @i AS INT = 0 SET @val2 = 'abc' DECLARE @tbl TABLE ( [ID] [int] IDENTITY(1,1) NOT NULL, [val2] VARCHAR(50) NULL, [datum] [datetime] NULL ) --INSERT INTO @tbl SELECT @val2, DATEADD(DAY, @i, @Date) UNION ALL SELECT @val2, DATEADD(DAY, @i, @Date) 

In this query, I need to insert the dates, starting from the given date, to the number of the value assigned to the variable "@val". Thus, in this case, 20 rows must be inserted into the table, starting from '02 -05-2016 ', and then increasing the number by 1 day for each row.

How can I do this in a single expression without any loops or multiple insert statements?

Answer 1

You can use the number table, if you have one, use master.dbo.spt_values if you want to have values ​​before 2048 or create your own. In this case, you can use master.dbo.spt_values :

 DECLARE @val AS INT=20, @val2 AS VARCHAR(50); DECLARE @Date AS DATETIME = CONVERT(DATETIME,'02-05-2016'); SET @val2 = 'abc' INSERT INTO dbo.YourTable SELECT @val2, DATEADD(DAY,number,@Date) FROM master.dbo.spt_values WHERE type = 'P' AND number <= @val; 

Although, since it starts from scratch, you end up with 21 lines

Answer 2

In addition to the detailed answer that I pointed out in my comment, this is a short idea:

 DECLARE @start INT=0; DECLARE @end INT=19; --0 to 19 are 20 days DECLARE @StartDate DATE={d'2016-01-01'}; --Create a List of up to 1.000.000.000 rows on the fly --This is limited by start and end parameter ;WITH x AS(SELECT 1 AS N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tbl(N))--10^1 ,N3 AS (SELECT 1 AS N FROM x CROSS JOIN x AS N2 CROSS JOIN x N3) --10^3 ,Tally AS(SELECT TOP(@end-@start +1) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) + @start -1 AS Nr FROM N3 CROSS JOIN N3 N6 CROSS JOIN N3 AS N9) --INSERT INTO your_table SELECT @val2 --your @val2 here as a constant value ,DATEADD(DAY,Nr,@StartDate) FROM Tally 

Answer 3

You can use recursive CTE .

 DECLARE @i INT = 1 , @m INT = 19 , @d DATETIME2 = '2016-05-02'; WITH i AS ( SELECT 0 AS increment UNION ALL SELECT i.increment + @i FROM i WHERE i.increment < @m ) SELECT i.increment , DATEADD(DAY, i.increment, @d) FROM i OPTION (MAXRECURSION 100); 

Check out the OPTION (MAXRECUSION 100) tooltip below, which is not strictly necessary, but I turned it on to illustrate how it works. By default, there is a limit of 100 results for this method, therefore without this operator and if @m was a large number, for example. 1000 , then SQL generates an error. You can set the lmit value to 0 , which means unlimited, but only after testing your code because it can get stuck in an infinite loop this way (therefore, the limit exists by default).