T-SQL-在存储过程的临时表中填充数据

Question

我需要帮助处理我目前正在处理的存储过程。基本上，存储过程运行良好，可以获得所需的数据。我想保持这种功能性，在存储过程中添加新的临时表，并用我获得的数据填充这个临时表。

我不知道在特定的存储过程中应该在哪里/如何使用INSERT INTO SELECT语句或SELECT INTO。下面我正在提交我的症状存储过程：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetHourReportData]
@dateFrom   SMALLDATETIME,
@dateTo     SMALLDATETIME,
@hourFrom   INT,
@hourTo     INT
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE tempTable
(
    -- fields
)
IF (DATEDIFF(DAY, @dateFrom, @dateTo) = 0)
    BEGIN
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    END
ELSE IF (DATEDIFF(DAY, @dateFrom, @dateTo) = 1)
    BEGIN
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    UNION ALL
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions
    END
ELSE
    BEGIN
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions    
    UNION ALL
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    UNION ALL
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions
    END
END

谢谢。任何帮助都将不胜感激。

Answer 1

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetHourReportData]
@dateFrom   SMALLDATETIME,
@dateTo     SMALLDATETIME,
@hourFrom   INT,
@hourTo     INT
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #tempTable --notice the #
(
    -- fields
)
IF (DATEDIFF(DAY, @dateFrom, @dateTo) = 0)
    BEGIN
        insert into #tempTable
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    END
ELSE IF (DATEDIFF(DAY, @dateFrom, @dateTo) = 1)
    BEGIN
        insert into #tempTable
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    UNION ALL
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions
    END
ELSE
    BEGIN
        insert into #tempTable
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions    
    UNION ALL
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    UNION ALL
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions
    END
END

Answer 2

CREATE TABLE tempTable
(
    -- fields
)
IF (DATEDIFF(DAY, @dateFrom, @dateTo) = 0)
    BEGIN
        INSERT INTO tempTable
        (SELECT -- fields
        FROM -- tables 
        WHERE -- conditions)
    END
ELSE IF (DATEDIFF(DAY, @dateFrom, @dateTo) = 1)
    BEGIN
        INSERT INTO tempTable
        (SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    UNION ALL
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions)
    END
ELSE
    BEGIN
        INSERT INTO tempTable
        (SELECT -- fields
        FROM -- tables
        WHERE -- conditions    
    UNION ALL
        SELECT -- fields
        FROM -- tables 
        WHERE -- conditions
    UNION ALL
        SELECT -- fields
        FROM -- tables
        WHERE -- conditions)
    END
END
select * from tempTable