Loop through result set - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

Loop through result set

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-05-07 : 23:15:20

I have a result set from a SQL query. The result set will have multiple rows. I need to get a count of the rows and then perform a SQL INSERT using specific columns as arguments in a SQL insert loop. I hope that wasn't too convoluted of an explanation.

For example:


DECLARE @i int
SET @numrows = (SELECT COUNT(*) cnt FROM Patient WHERE ClientID = 5001)

SELECT FirstName, LastName, DOB FROM Patient WHERE ClientID = 5001

**make believe code just to demonstrate what I'm trying to accomplish**

for @i = 1 to @numrows

--do some INSERT function here using specific fields from the above SELECT query as arguments

NEXT i

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-05-08 : 01:52:10

If the INSERT is into an existing table , use INSERT INTO

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

Kristen
Test

22859 Posts

Posted - 2015-05-08 : 04:21:23

Shouldn't need a LOOP for this (in a relational database), might be able to work around that with something like:


INSERT INTO MyTable
SELECT FirstName, LastName, DOB 
FROM Patient
     , MyTallyTable AS T
WHERE ClientID = 5001
      AND T.Number BETWEEN 1 AND @numrows

There are various solutions available to getting a Tally Table, depending on what the maximum value of @numrows might be.

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-05-08 : 08:25:06

quote:
Originally posted by jackv

If the INSERT is into an existing table , use INSERT INTO

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

Sorry I wasn't clear ... this involves two different tables. I'm selecting data from one and then using some of that data for the insert into another.

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-08 : 09:32:24

@Kristen's solution should work for you

Gerald Britton, MCSA
Toronto PASS Chapter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources