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

Question

I have to insert a specific 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 have to insert dates starting from a given date till the number of value assigned to the variable '@val'. So, in this case, 20 rows need to be inserted into the table starting from '02-05-2016' and then date increasing 1 day for each row.

How can I do it in a single statement without any looping or multiple insert statements?

Obviously you are trying to create a list of running numbers / dates: [In one of my answer](http://stackoverflow.com/a/32474751/5089204) you find an example using a `Tally-CTE` to create a list of numbers, dates and a lot of side-info for each day. Think this is what you need... — Shnugo, Sep 20 '16 at 13:39

score 1 · Accepted Answer · answered Sep 20 '16 at 13:41

You can use a numbers table if you have one, use master.dbo.spt_values if you want one that has values till 2048, or create one of your own. In this case, you could 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;

Though since this starts at zero, you'll get 21 rows as a result

This is the fastest and most feasible solution, thanks. – Hemant Sisodia Sep 21 '16 at 09:43 — Hemant Sisodia, Sep 21 '16 at 09:43

score 0 · Answer 2 · answered Sep 20 '16 at 13:45

Besides the detailed answer I pointed to in my comment, this is the idea in short:

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

knuckles · Answer 3 · 2016-09-20T15:33:40.223

0

You could use a 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);

Note the OPTION (MAXRECUSION 100) hint at the bottom, which is not strictly necessary but I have included it to illustrate how it works. By default, there is a limit of 100 results using this method, so without this statement and if @m were a large number e.g. 1000 then SQL would generate an error. You can set the lmit to 0 which means unbounded, but only do this after testing your code, because it can get stuck in an infinite loop this way (which is why the limit exists by default).

edited Sep 20 '16 at 15:33

answered Sep 20 '16 at 13:50

knuckles

388
2
9

This *modern, set based way* is about 3-5 times slower than the `CROSS JOIN` approach. Recursive CTEs are a hidden RBAR. They are great to fiddle down a tree but not the best tool to create a running list... – Shnugo Sep 20 '16 at 14:31
@Shnugo there you go, removed the superfluous adjectives – knuckles Sep 20 '16 at 15:36

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

3 Answers3

Linked