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

Question

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?

Gordon Linoff · Accepted Answer · 2020-03-19 16:22:01Z

24

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.

edited Mar 19, 2020 at 16:22

answered Mar 19, 2020 at 16:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

3 Comments

Ashkan Mobayen Khiabani Over a year ago

Ohhhh yeah, I remembered it now, actually had used it this way very long ago. many many thanks

Jeroen Mostert Over a year ago

@AshkanMobayenKhiabani: Worth noting is that VALUES can be a complete replacement for a TABLE declaration, depending on how the table is used (SELECT * FROM (VALUES (1), (2), (3), (4), (5)) T(Id)), especially if it's "hard-coded" (though not necessarily if used multiple times, of course).

Ashkan Mobayen Khiabani Over a year ago

@JeroenMostert yeah I know that, but the reason I'm doing this way is because it is going to be used many many times for tens of queries so I want to be able to easily change it for all. the reason I asked the question was for learning purpose mostly

John Cappelletti · Accepted Answer · 2020-03-19 16:21:54Z

3

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

answered Mar 19, 2020 at 16:21

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related