CODEWITHSUNDEEP
sqlsql-server
David
David

Reputation: 287

How to store result set of a SQL Statement into a variable?

I am writing a SP and I want to know if I can store a result of a statement into a variable upon which I will trigger another query.

For instance, below query gives me an id and I need to use this id for another Insert statement. 

variable id = Select id from tableA where myCondition = 'this';
Insert into tableB values(id, a, ab ,c);

I am not sure if something exists like this in SQL Server where I can define a variable in this case id is of type int and user it later.

Thanks

Upvotes: 3

Views: 4232

Answers (2)

Zorkolot
Zorkolot

Reputation: 2017

If you have more than 1 row returned in your select statement then you can use a table variable:

DECLARE @temp TABLE (id int) 

INSERT INTO @temp (id)
SELECT id FROM tableA WHERE myCondition = 'this'

INSERT INTO tableB
SELECT T.id, a, ab, c
  FROM @temp T  ...

Upvotes: 3

Ousmane D.
Ousmane D.

Reputation: 56393

Simply declare a variable and assign the result of the query to the variable.

DECLARE @Id INT = (SELECT id FROM tableA WHERE myCondition = 'this');

then you can do:

INSERT INTO tableB values(@Id, a, ab ,c);

Upvotes: 4

Related Questions