Insert Values into Table Using Parameter

Question

I am creating a temp table that has one column and I am trying to add multiple rows of data into that column. Is it possible to put these string/varchar values into a parameter/variable to be inserted into the table? Instead of listing them out ('x'),('y'),('z') for example. Thanks

Answer 1

You can have user defined Table-Type as shown below:

User-Defined Table Type can be created via script below or via SSMS as shown in image:

CREATE TYPE [dbo].[my_array] AS TABLE(
    [string_value] [varchar](500) NOT NULL -- Define your string column
)
GO

and use it as below:

declare @myList as [my_array];
insert into @myList values('x'),('y'),('z'); -- You can insert from existing table 

Retrieve the values as below:

select * from @myList

Fiddler is here.