CODEWITHSUNDEEP
t-sqlsql-inserttempunion-all
Dear Deer
Dear Deer

Reputation: 99

T-SQL - populating data in temp table in stored procedure.

I need help with my stored procedure which I'm currently working on. Basically stored procedure works fine and gets me required data. I would like to keep this funcionality and add new temporary table within stored procedure and populate this temp table with the data that I get.

I don't know where/and how should I use INSERT INTO SELECT statement or SELECT INTO in my particular stored procedure. Below I'm submiting my symplified stored procedure :

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

Thanks. Any help will be appreciated.

Upvotes: 3

Views: 1178

Answers (2)

db9dreamer
db9dreamer

Reputation: 1715

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

Upvotes: 1

Mikhail Timofeev
Mikhail Timofeev

Reputation: 2169

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

Upvotes: 0

Related Questions