在SQL Server中使用DateTime值自动填充表 [英] Auto-populating a table with DateTime values in SQL server

问题描述

我有一个名为 Appointment 的表，其中要显示（60分钟间隔）中的约会空档c $ c>工作日从上午10点到下午3点 ，直到八月。

I have a table called Appointment in which I want to display appointment slots (60min intervals) from 10am to 3pm for Weekdays till August.

下面是我的表结构：

AppointmentID
Date
StartTime
EndTime

样本记录：

1
2015-2-2
11:45:34
12:45:34

我知道我可以手动执行 Insert 语句，但是我想编写一个SQL查询，

I know I can do Insert statements manually but I wanted to write a SQL query that would do that automatically and would appreciate your help.

推荐答案

尝试以下操作：

insert Appointment([Date],StartTime,EndTime)
select cast(DATEADD(day, n, StartTime) as date)
, cast(StartTime as time)
, cast(dateadd(hour,1,DATEADD(day, n, StartTime)) as time) 
from
(
    select DATEADD(hour, n, '2015-01-01 10:00') StartTime
    from dbo.generate_series(0, DATEDIFF(hour,'2015-01-01 10:00','2015-01-01 15:00'), 1, 0)
) StartTimes
cross join dbo.generate_series(0, DATEDIFF(day,'2015-01-01','2015-08-31'), 1, 0)
order by AppointmentStart

使用来自generate_series / 2014/09/17 / t-sql-generate-series-getting-a-list-of-numbers-in-a-given-range / rel = nofollow> https://developer42.wordpress.com/ 2014/09/17 / t-sql-generate-series-get-a-number-in-a-given-range /

Uses generate_series from: https://developer42.wordpress.com/2014/09/17/t-sql-generate-series-getting-a-list-of-numbers-in-a-given-range/

create function dbo.generate_series
(
      @start bigint
    , @stop bigint
    , @step bigint = 1
    , @maxResults bigint = 0 --0 = unlimited
)
returns @results table(n bigint)
as
begin

    --avoid infinite loop (i.e. where we're stepping away from stop instead of towards it)
    if @step = 0 return
    if @start > @stop and @step > 0 return
    if @start < @stop and @step < 0 return

    --ensure we don't overshoot
    set @stop = @stop - @step

    --treat negatives as unlimited
    set @maxResults = case when @maxResults < 0 then 0 else @maxResults end

    --generate output
    ;with myCTE (n,i) as
    (
        --start at the beginning
        select @start
        , 1
        union all
        --increment in steps
        select n + @step
        , i + 1
        from myCTE 
        --ensure we've not overshot (accounting for direction of step)
        where (@maxResults=0 or i<@maxResults)
        and
        (
               (@step > 0 and n <= @stop)
            or (@step < 0 and n >= @stop)
        )  
    )
    insert @results
    select n 
    from myCTE
    option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this 

    --all good  
    return

end

这篇关于在SQL Server中使用DateTime值自动填充表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！