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
Go to Top of Page

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.
Go to Top of Page

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)
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -