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

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

- Dear Deer

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

- dav1dsm1th

---

谢谢 :) 那正是我需要的。 - Dear Deer

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

- Mikhail Timofeev