我想為日期范圍內的每個日期插入多行
我有一張桌子
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]
從單個陳述句中，我想在 6 行以下的情況下從以下資訊中為每個日期插入多行
Declare
@StartDate smalldatetime = '2022-01-01'
, @EndDate smalldatetime = '2022-01-06'
, @BookedFor nvarchar(50) = 'Mr Smith'
, @DeskName varchar(10) = '3'
有看起來像的行
| 預訂ID | 預訂 | 預訂時間 | 桌名 |
|---|---|---|---|
| 1 | 史密斯先生 | 2022-01-01 | 3 |
| 2 | 史密斯先生 | 2022-01-02 | 3 |
| 3 | 史密斯先生 | 2022-01-03 | 3 |
| 4 | 史密斯先生 | 2022-01-04 | 3 |
| 5 | 史密斯先生 | 2022-01-05 | 3 |
| 6 | 史密斯先生 | 2022-01-01 | 3 |
任何指標？
uj5u.com熱心網友回復：
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
在兩個日期之間創建天數
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/418529.html
標籤：
上一篇：如何在更新中獲取選定列的值