Can TSQL select into an array or variable?

Question

Dear All,

Can a select command put its result into an array (or a variable) as follows?

 Select * from student into array myArray          (note: this is not a correct statement)

Answer 1

Databases don't have object types like array, but tables.
You can define a variable as table and insert with a select statement data into, example:

declare @test table (object_id int, name sysname);

insert into @test
select object_id, name
from sys.objects;

select *
from @test

Answer 2

The syntax is almost correct

SELECT *
INTO Array
FROM Student

This will create a new table called Array which is a copy of Student. But it is a table, not an array.

I think Postgres has arrays, but I don't understand why. As Olaf says, in a relational database you work with tables.

Answer 3

Hi，@BenTam-3003
We cannot insert the queried data into the array，which is not supported in SQL server.
For more details about 'insert into',please refer to this document.
But we can use local table variable DECLARE @TempTable or temporary table #TempTable to store the result of select command .
You can also combine query results into strings or XML documents,like this:

DECLARE @STR VARCHAR(8000)  
SELECT @STR=ISNULL(@STR+',','')+Stu_ID FROM Student  
PRINT @STR  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

not script, that will create a C# array or a C# variable.

Nearly, you can use a data reader, see examples at SqlCommand Class