0

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)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Haze
  • 3
  • 3
  • [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) - you should **always** provide a length for any `varchar` variables and parameters that you use – marc_s Apr 03 '23 at 03:55
  • Hi marc_s, i did not declare the length for simplicity purpose. Anyway, thank you for the info – Haze Apr 03 '23 at 04:01
  • If you don't declare a length - do you know how long that variable is going to be? It will be exactly **1** character long - typically **NOT** what you want ! – marc_s Apr 03 '23 at 04:09
  • Also you should declare which columns you are inserting into. What happens if someone adds a column or reorders them? – Charlieface Apr 03 '23 at 12:19

1 Answers1

0

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
Craig
  • 1,123
  • 3
  • 13