[SOLVED] Insert values into table from TVP and variable

Insert values into table from TVP and variable

I have an input TVP

@_inputTvp TableType Readonly

and local variable

declare @_localVar varchar

I know how to insert TVP to a table, and variable value to a table. But now I have 2 source of data (TVP and local variable) to insert to table. Something like this?

insert into A_Table
values (@localVar, select col1, col2, col3 from @inputTvp)

Solution

When inserting, using VALUES(...) produces just a single row (of the corresponding values) to insert. VALUES(...) is not how you extract rows from another table (or tables) to insert into your target table.

Assuming that you just want to insert all of the rows from the @inputTvp table variable, but that the first column of each inserted row should have the value from your @localVar variable, then you would use the following:

INSERT INTO A_Table
SELECT @localVar, col1, col2, col3
FROM @intputTvp