在INSERTion过程中向表值参数添加额外的列

Question

给定以下表格类型：

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

我将这个表类型传递给一个存储过程。在这个存储过程中，我想用一个额外的id someId (对于TestType中的所有行都一样)将来自TestType的数据添加到一个表dbo.someTable中。向TestType中的所有行添加someId的最佳方法是什么

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

Answer 1

只需从table参数中执行SELECT，并添加一个包含常量值的列：

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

Answer 2

这是最有效的方法。

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
          @SomeId
         ,SomeNumber
         ,SomeOtherNumber
    END;
GO