Loop SQL results inside another SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Loop SQL results inside another SQL statement

Author

Topic

hissein
Starting Member

5 Posts

Posted - 2012-08-02 : 01:18:30

Hello guys,

I want to run an SQL statement and use the record results in another SQL statement while its in the loop. i normally do it in ASP script, im not sure if i can do it directly on via T-SQL,

example as below:

SQL = SELECT id1, nationality_id1 FROM SourceFilename Order by id
Set rs = db.execute(SQL)

Do until rs.EOF
nationality_id1 = rs("nationality_id")
id1 = rs("id")

SQL2 = "Update DestinationTableName Set nationality_id = nationality_id1 WHERE ER_Staff_id = " id1 db.Execute(SQL2)

response.Flush
rs.MoveNext
Loop

Thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-08-02 : 01:33:33

Read up on CURSORS in SQL Server BOL. You can create a reordset in a CURSOR, move through the CURSOR , doing something based on every row.
CURSORS are very useful , but under the right circumestances can be slower than a SET based approach. Consider a SET based approach first .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

hissein
Starting Member

5 Posts

Posted - 2012-08-02 : 02:40:29

Hi Jack,

Thanks for your reply, does CURSORS works directly on SQL server?

How do you advise me to start on this, any training materials you recommend?

Thanks

quote:
Originally posted by jackv

Read up on CURSORS in SQL Server BOL. You can create a reordset in a CURSOR, move through the CURSOR , doing something based on every row.
CURSORS are very useful , but under the right circumestances can be slower than a SET based approach. Consider a SET based approach first .

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-08-02 : 07:26:14

Learn neither loops nor cursors but SQL and JOINs and set-based processing:

UPDATE B 
SET nationality_id = A.nationality_id1
FROM SourceFilename A
INNER JOIN DestinationTableName B ON A.id=B.ER_Staff_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources