insert values into table valued parameters in sql server

Question

I am seeing an error when I insert values into user type table. The type created successfully, but I am not able to insert values into that type.

How to insert values into that table type?

CREATE TYPE ProductType1 AS TABLE
(
    fld_SkillTargetID int
)

I'm trying to insert values into that table type using this statement:

declare @par as ProductType1
insert into @par values('fld_SkillTargetID')values(5166)

but am getting error. I am not able to insert the values into that table type.

When saying you get an error, it is a good idea to include the error. — Oded
– Oded, Commented Dec 5, 2012 at 11:18

Oded · Accepted Answer · 2012-12-05 11:17:06Z

11

You have one too many values - and you don't need to escape the field name:

insert into @par (fld_SkillTargetID)
values(5166)

answered Dec 5, 2012 at 11:17

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

but we cannot insert more than 2000 parameters via tvp. is there a better way to handle