CODEWITHSUNDEEP
sqlsql-server
David Elliott
David Elliott

Reputation: 81

Insert multiple rows from a date range

I want to insert multipe rows with one for each date in a date range

I have a table

CREATE TABLE [dbo].[Booked] (
    [BookedId] [bigint] IDENTITY (1, 1) NOT NULL,
    [BookedFor] [nvarchar] (50) NOT NULL,
    [BookedWhen] [smalldatetime] NOT NULL,
    [DeskName] [varchar] (10) NOT NULL
) ON [PRIMARY]

From a single statement I want to insert multiple rows in the case below 6 rows one for each date from the following information

Declare
  @StartDate smalldatetime = '2022-01-01'
, @EndDate   smalldatetime = '2022-01-06'
, @BookedFor nvarchar(50)  = 'Mr Smith'
, @DeskName  varchar(10)   = '3'

To have row that look like

BookedId BookedFor BookedWhen DeskName
1 Mr Smith 2022-01-01 3
2 Mr Smith 2022-01-02 3
3 Mr Smith 2022-01-03 3
4 Mr Smith 2022-01-04 3
5 Mr Smith 2022-01-05 3
6 Mr Smith 2022-01-01 3

Any pointers?

Upvotes: 0

Views: 1931

Answers (1)

Maxim
Maxim

Reputation: 869

CREATE TABLE [#Booked] (
    [BookedId] [bigint] IDENTITY (1, 1) NOT NULL,
    [BookedFor] [nvarchar] (50) NOT NULL,
    [BookedWhen] [smalldatetime] NOT NULL,
    [DeskName] [varchar] (10) NOT NULL
)


Declare
  @StartDate smalldatetime = '2022-01-01'
, @EndDate   smalldatetime = '2022-01-06'
, @BookedFor nvarchar(50)  = 'Mr Smith'
, @DeskName  varchar(10)   = '3'


;WITH Dates(day) AS 
(
    SELECT CAST(@StartDate as Date) as day
    UNION ALL
    SELECT CAST(DATEADD(day, 1, day) as Date) as day
    FROM Dates
    WHERE CAST(DATEADD(day, 1, day) as Date) < @EndDate
)
INSERT INTO #Booked 
SELECT @BookedFor, [day], @DeskName FROM dates;

SELECT * FROM #Booked

Create days between two dates

Upvotes: 2

Related Questions