How to loop through a select and using the return value in an insert query in SQL Server

Question

I'm creating a stored procedure in SQL server and want to achieve the following:

I have to look up all the ResourceID's (could be any number of resources) for a certain ClientID in the Resources table. Then I have to insert a row in the Planning table for each ResourceID found.

So I'll have something like

WHILE EXISTS (SELECT ResourceID AS @ResourceID FROM Resources WHERE ClientID = @ClientID)
BEGIN
    INSERT INTO Planning (ResourceID, Date)
    VALUES (@ResourceID, @Date)
NEXT
END

I have already found that using a cursor might be the way, but that doesn't lead me to anything usable

for future reference: anytime you find yourself looking for a loop or cursor in SQL Server (especially a cursor) there is almost always a better way. — Johnie Karr
– Johnie Karr, Commented Feb 27, 2012 at 13:39
To help you learn about those better ways:wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them — HLGEM
– HLGEM, Commented Feb 27, 2012 at 15:54

Oded · Accepted Answer · 2012-02-27 13:39:34Z

3

No need for loops:

INSERT INTO Planning (ResourceID, Date)
SELECT ResourceID, GETDATE()
FROM Resources 
WHERE ClientID = @ClientID

In general, good practice is to avoid loops/cursors in SQL. Use set based options whenever possible.

edited Feb 27, 2012 at 13:39

answered Feb 27, 2012 at 13:35

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user596075 · Accepted Answer · 2012-02-27 13:35:54Z

2

No need for a cursor. Keep it set-based:

insert into planning (resourceid, date)
select resourceid, @SomeDate
from resources
where clientid = @ClientId

answered Feb 27, 2012 at 13:35

user596075

Collectives™ on Stack Overflow

How to loop through a select and using the return value in an insert query in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related