sqlsql-servert-sqlsql-inserttable-valued-parameters

Add an additional column to table valued parameters during INSERTion


Given the following table type:

/* Create a table type. */
CREATE TYPE TestType
   AS TABLE
      (someNumber      int,
       someOtherNumber int);
GO

I pass this table type to a stored procedure. Inside this stored procedure I want to add the data from TestType with an additional id someId (same for all rows in TestType) to a table dbo.someTable. What is the best way to add someId to all rows in TestType?

CREATE PROCEDURE dbo.Test @TVP TestType READONLY
AS
    BEGIN
    SET NOCOUNT ON

    DECLARE @someId int;
    SET @someId = 10;

    INSERT INTO dbo.someTable
        (someId,
         someNumber,
         someOtherNumber)
        VALUES
         -- here I want to add all rows from @TVP.
         -- However, @TVP only has the columns someNumber and someOtherNumber.
         -- The column someId is missing. What is the most efficient way
         -- to add the @someId to all rows during insert?
         -- Note that @someId shall be the same value for all rows in @TVP.
    END;
GO

Solution

  • Just SELECT from the table argument, and add a column with the constant value:

    INSERT INTO dbo.someTable (someId, someNumber, someOtherNumber)
    SELECT @someId, someNumber, someOtherNumber 
    FROM @TVP