SQL Server question stored procedure loops

Question

I have to run multiple queries that are dependent on each other

select a from a ( result has multiple rows)
select b from b  ( result has multiple rows)

the for each row in that query i need to run this

select c from c where c.a=a amd c.b=b

 insert  c into d

kinda like:

select a from a ( result has multiple rows)
select b from b  ( result has multiple rows 
  for a in a
      for b in b
          select c from c where c.a=a amd c.b=b
          insert  c,a,b into d

is this possible to do as an stored procedure?

thank you

table a has multiple rows in the result and so as b

Birby
– Birby

2010-11-08 16:15:28 +00:00
Commented Nov 8, 2010 at 16:15 — Birby
– Birby, Commented Nov 8, 2010 at 16:15
the final result should look like this table d: c,a,b

Birby
– Birby

2010-11-08 16:15:55 +00:00
Commented Nov 8, 2010 at 16:15 — Birby
– Birby, Commented Nov 8, 2010 at 16:15

Joel Coehoorn · Accepted Answer · 2010-11-08 16:12:20Z

4

Stored procedure? Sure. Shoot, it's even possible in one statement:

INSERT INTO d (c)
  SELECT c FROM c WHERE c.a IN (SELECT a FROM a) AND c.b IN (SELECT b FROM b)

You can probably even do a little better with a join, but to write that I need to know more about how the tables relate.

answered Nov 8, 2010 at 16:12

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

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

Comments

clyc · Accepted Answer · 2010-11-08 16:09:40Z

3

I'm pretty sure you can write this query without using any loops in SQL.

answered Nov 8, 2010 at 16:09

clyc

2,45014 silver badges15 bronze badges

Comments

Quassnoi · Accepted Answer · 2010-11-08 17:25:43Z

1

INSERT
INTO    d
SELECT  c.*
FROM    a
CROSS JOIN
        b
JOIN    с
ON      c.a = a.id
        AND c.b = b.id

answered Nov 8, 2010 at 17:25

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Collectives™ on Stack Overflow

SQL Server question stored procedure loops

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related