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
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