store result of select query into array variable

Question

I want to store the result of this sql query in variable @a. The result contains 17 rows. How to edit this code in order to store the rows in @a?

declare @a uniqueidentifier
select EnrollmentID into @a  from Enrollment

Answer 1

You cannot store 17 values inside a scalar variable. You can use a table variable instead.

This is how you can declare it:

DECLARE @a TABLE (id uniqueidentifier)

and how you can populate it with values from Enrollment table:

INSERT INTO @a 
SELECT EnrollmentID FROM Enrollment

Answer 2

You should declare @a as a Table Variable with one column of type unique identifier as follows:

DECLARE @a TABLE (uniqueId uniqueidentifier); 

INSERT INTO @a
SELECT EnrollmentID 
FROM Enrollment;