This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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)
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
Do you need the array in SQL for further usage, or in C#, after transferring the results?
Hi @Viorel
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.
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://learn.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#.
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.
Hi @Viorel
Thanks for your reply and links.
Hi @Viorel
How about c# LINQ? May be it could create a c# array.
It may be possible to do in LINQ, which is something that runs in the C# code and generates the SQL code for you behind your back. However, the tag for this question is sql-server-transact-sql. Maybe you post a new question and use some suitable dotnet tag.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 52%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ELM%3C/text%3E%3C/svg%3E)
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
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
not script, that will create a C# array or a C# variable.
Nearly, you can use a data reader, see examples at SqlCommand Class
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.
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.