Insert a constant value to table in stored procedure

Question

I have the following stored procedure:

DECLARE @BeginDate1 datetime, @EndDate1 datetime
set @BeginDate1 = '04-01-2012'
set @EndDate1  = '03-31-2013'
BEGIN

    INSERT INTO MTBUR_Type_Stage
       SELECT 
          R.Type, [Hours], 
          LEFT(dbo.fn_GetPartName(R.PartID),CHARINDEX('-',dbo.fn_GetPartName(R.ACSS_PartID), 1) - 1) AS 'Part No', 
          Count(s.status) AS NumberUnscheduled, 
          ([Hours]/Count(s.status)) AS MTBUR
       FROM 
          Repair R
       INNER JOIN 
          Conversion C ON (R.Performed = C.Performed)
                       AND (R.Confirmed = C.Confirmed)
       INNER JOIN 
          Status S ON C.StatusID = S.StatusID
       INNER JOIN 
          #MTBUR_Hrs_Temp TEMP ON LEFT(dbo.fn_GetPartName(R.PartID),CHARINDEX('-',dbo.fn_GetPartName(R.PartID), 1) - 1) = TEMP.productNo
                               AND R.Type = TEMP.Type  
        WHERE 
           (R.Received BETWEEN @BeginDate1 AND @EndDate1)
           AND (S.Status = 'UNSCHEDULED')
        GROUP BY 
           LEFT(dbo.fn_GetPartName(R.PartID),CHARINDEX('-',dbo.fn_GetPartNaame(R.PartID), 1) - 1), [Hours], R.Type
        ORDER BY 
           R.Type, 
           LEFT(dbo.fn_GetPartName(R.ACSS_PartID),CHARINDEX('-',dbo.fn_GetPartID(R.PartID), 1) - 1) ASC

    DROP TABLE #MTBUR_Hrs_Temp
END

The table being inserted to (MTBUR_Type_Stage) has columns named EndingDate and EndingQuarter. EndingDate needs to have the same date (constant) which is equal to @EndDate and EndingQuarter needs to have a constant value of 1

How can I script this in my stored procedure so that ALL rows are populated with these constants in the relevant columns in MTBUR_Type_Stage are populated with these constants?

The following is the DDL for the table I am inserting into:

CREATE TABLE [dbo].[MTBUR_ByType_StageTbl]
(
    [Type] [nvarchar](25) NULL,
    [Hours] [float] NULL,
    [Part No] [varchar](15) NULL,
    [UnscheduledRemovals] [int] NULL,
    [MTBUR] [float] NULL,
    [EndingDate] [datetime] NULL,
    [EndingQuarter] [int] NULL
) ON [PRIMARY]

Pieter Geerkens · Accepted Answer · 2014-07-23 00:42:28Z

3

As simple as this:

insert MTBUR_Type_Stage(EndingDate,EndingQuarter)
values (@EndDate,1)
;

or this

insert MTBUR_Type_Stage(EndingDate,EndingQuarter)
select @EndDate,1

Expanded for more fields:

insert MTBUR_Type_Stage( 
    [Type],
    [Hours],
    [Part No],
    [UnscheduledRemovals],
    [MTBUR],
    [EndingDate],
    [EndingQuarter] [int] NULL
)
select 
    'x',1.00,'part 1', 0, 2.34,
    @EndDate,1

edited Jul 23, 2014 at 0:42

answered Jul 23, 2014 at 0:24

Pieter Geerkens

11.9k2 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

J.S.Orris Over a year ago

Thank you...I don't know why I can figure out complex stuff but ALWAYS miss the simplest things.

J.S.Orris Over a year ago

However, I am getting the following error: Msg 213, Level 16, State 1, Procedure sp_GetMTBUR_ByType_2013_EndQ1, Line 35 Insert Error: Column name or number of supplied values does not match table definition

Pieter Geerkens Over a year ago

@JeffOrris: You must have additional NOT NULL columns in the table for which values must be supplied. I can't see the DDL for it in your post.

J.S.Orris Over a year ago

I added the DDL for the StageTbl

Pieter Geerkens Over a year ago

@JeffOrris: Simply drive it with a Tally Table (or Number Table) with the appropriate Row Count to do that. Google Tally Table and select a post by either Jeff Moden or Itzak.

|

Collectives™ on Stack Overflow

Insert a constant value to table in stored procedure

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related