question

BenTam-3003 avatar image
0 Votes"
BenTam-3003 asked LiHongMSFT-3908 commented

Can TSQL select into an array or variable?

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)
sql-server-transact-sql
· 10
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Do you need the array in SQL for further usage, or in C#, after transferring the results?


0 Votes 0 ·

Hi @Viorel-1

Thanks for your reply. I am a C# programmer. I am thinking about is there is any SQL command, not script, that will create a C# array or a C# variable.

0 Votes 0 ·

I am not sure if there is any SQL command, not script, that will create a C# array or a C# variable.

Theoretically you can use the BCP command-line utility to export the data to a file, which can be then loaded to C# arrays: https://docs.microsoft.com/en-us/sql/tools/bcp-utility.

You can also change the "Results To" option from Query menu in Management Studio to output the results to a file.

See also: https://www.sqlservercentral.com/articles/8-ways-to-export-sql-results-to-a-text-file.

Or you can use the usual technique, based on SqlConnection, SqlCommand, etc. classes, to get the data from SQL in C#.


0 Votes 0 ·
Show more comments

I am thinking about is there is any SQL command, not script, that will create a C# array or a C# variable.

This suggests that you have gotten a full understanding of a situation. You write a C# program that runs on your computer. You call SQL Server which may be running on your local computer, or it could be running on a computer somewhere else. In any case, it is always a separate process. Your program communicates with SQL Server over the TDS protocol, which is a protocol specific to SQL Server. SQL Server has no knowledge about your C# code. All it knows that there is a client that sends in SQL code and SQL Server sends back data in the format that follows from the TDS specification.

Sure, you can create arrays and whatnots from the data you get from SQL Server. But all that has to happen in the C# code.

0 Votes 0 ·
Show more comments

Hi @BenTam-3003
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
If you still have doubts about your issue, please feel free to share with us.
Thank you for understanding!

Best regards,
LiHong

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

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.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.