CODEWITHSUNDEEP
sqlsql-servert-sql
Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34180

How to declare a table variable with existing data in sql server

I want to declare a table variable in sql server which will have some hard coded values in it. I tried:

DECLARE @tbl TABLE(Id int) = (1, 2, 3, 4, 5) -- gives error

and this didn't work either:

DECLARE @tbl TABLE(Id int) 
INSERT INTO @TBL SELECT * FROM (1, 2, 3, 4, 5) -- also gives error

isn't there any way to create a table with hard coded existing values?

Upvotes: 7

Views: 39047

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

The syntax you want is:

DECLARE @tbl TABLE(Id int);

INSERT INTO @tbl (id)
   VALUES (1), (2), (3), (4), (5);

Here is a db<>fiddle.

Upvotes: 18

John Cappelletti
John Cappelletti

Reputation: 82020

In case your data is a string

DECLARE @tbl TABLE(Id int) 
INSERT INTO @TBL 
 SELECT value from string_split('1, 2, 3, 4, 5',',')

 Select * from @tbl

Upvotes: 3

Related Questions