CODEWITHSUNDEEP
sql-server
DoArNa
DoArNa

Reputation: 532

How to enter multiple variable values at once?

I have a situation like this:

Declare @studentId INT = 12;
INSERT INTO someTable (col1, col2)
SELECT @studentId, someValueForCol2 from anotherTable where isActive = 1

the studentId variable takes 10 different values. One way is to manually change the variable's value and execute the query 10 times, however it feels like this is not the right way to do this. Is there a way to give all those 10 values to the query on the same time? To make it clear: If the select statement returns 20 records than it would have to be 200 inserts in database, 20 records for each studentId. Thank you

Upvotes: 0

Views: 24

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

You can use a table valued constructor for this. Let's say you had two studentID values of 1 and 2. Here is how you could do this.

SELECT Students.StudentID
    , someValueForCol2 
from anotherTable 
cross join (values(1),(2))Students(StudentID)
where isActive = 1

Upvotes: 2

Related Questions