SQL Simulating a foreach

Question

I have to insert 7 rows into a table for each row found in a another table, I am currently doing this is my C# application, but it is dreadfully slow for a relatively small database.

I want to move this into one query, and the best method would be a foreach loop, SQL doesnt have this though so a WHILE loop will have to do.

However I cant even get to the insert part as I cannot loop through the rows, here is my SQL so far

DECLARE @cnt INT = 0;

SELECT ResNo FROM Res WHERE TSGrNo = 1;

print 'row cnt';
print @@RowCount; -- prints 0 even though 6 rows are returned

WHILE @cnt < @@Rowcount
BEGIN
     print @cnt;
     --In here I need to do this
     -- INSERT INTO tbl (_,tbl.ResNo,_, _, _)
        -- VALUES (_,Row.ResNo,_,_,_)
     SET @cnt = @cnt + 1;
END

Does anyone know a better and working way to do this?

EDIT:

This is where I am at now

DECLARE @_R1 INT = 7, @_Date INT = 20150608

SELECT  *
FROM    Res r
WHERE   NOT EXISTS
        (
        SELECT  * 
        FROM    Vis_ResR rr
        WHERE   rr.ResNo = r.ResNo
        AND rr.Date = @_Date
        )
AND r.TSGrNo = 1
AND r.R1 = @_R1

INSERT INTO Vis_ResR (R1, ResNo, Vis_ResR.Date, FrTm, ToTm)
VALUES (@_R1,r.ResNo,@_Date,0,0)

@_R1 and @_Date will be set before sending the query, I just don't understand how to make it INSERT using the r.ResNo values

Try to avoid solving SQL/database problems with object oriented programming techniques (loops, foreach, iterations, etc). RDBMS require set based operations to be most efficient. Can you please supply your table definitions, a sample of the data, and what you would like the output to look like? — Patrick Tucci, Jun 13 '15 at 21:06
Sorry I don't have time to write up an answer here, but this question may be helpful: http://stackoverflow.com/questions/11852782/t-sql-loop-over-query-results — phpmeh, Jun 13 '15 at 21:08
I understand that loops etc isn't the norm, but in this case I believe it is the correct thing to do... ResNo is an int, and is the only thing I need to get from each row inside the Res table. I then need to insert into another table using the ResNo as the second value. — Ted James, Jun 13 '15 at 21:11

Fabio · Accepted Answer · 2015-06-13T21:49:58.943

7

Why you cannot use SELECT statement in the INSERT INTO?

INSERT INTO tbl (tbl.ResNo)
SELECT ResNo FROM Res WHERE TSGrNo = 1

If SELECT statement return 6 rows, then 6 rows will be inserted

Adding Rows by Using INSERT and SELECT

Update after comment:
Inserting values if not already exist
Based on the version of SQL server you can use MERGE

DECLARE @_R1 INT = 7 
DECLARE @_Date INT = 20150608

MERGE INTO Vis_ResR AS Target
USING (SELECT ResNo 
       FROM Res 
       WHERE TSGrNo = 1 AND R1 = @_R1) AS Source 
ON Target.ResNo = Source.ResNo
WHEN NOT MATCHED THEN
    INSERT (R1, ResNo, Vis_ResR.Date, FrTm, ToTm) 
    VALUES (@_R1, Source.ResNo, @_Date, 0, 0);

MERGE (Transact-SQL)

Or if your SQL server version older then 2008 then try next

DECLARE @_R1 INT = 7
DECLARE @_Date INT = 20150608

INSERT INTO Vis_ResR (R1, ResNo, Vis_ResR.Date, FrTm, ToTm) 
SELECT @_R1, r.ResNo, @_Date, 0, 0
FROM Res r 
WHERE NOT EXISTS (SELECT * 
                  FROM Vis_ResR rr 
                  WHERE rr.ResNo = r.ResNo 
                  AND rr.Date = @_Date ) 
AND r.TSGrNo = 1 
AND r.R1 = @_R1

edited Jun 13 '15 at 21:49

answered Jun 13 '15 at 21:12

Fabio

31,528
4
33
72

Hurm, I didn't think of this. Would this also work with an IF statement? as I only need to INSERT if a row with the ResNo doesn't already exist – Ted James Jun 13 '15 at 21:14
@TedJames It would work if you filter out the rows from the `SELECT` statement, using `NOT EXISTS` for example) – ESG Jun 13 '15 at 21:16
@Fabio this is where I am up on now.... `DECLARE @_R1 INT = 7, @_Date INT = 20150608 SELECT * FROM Res r WHERE NOT EXISTS ( SELECT * FROM Vis_ResR rr WHERE rr.ResNo = r.ResNo AND rr.Date = @_Date ) AND r.TSGrNo = 1 AND r.R1 = @_R1 INSERT INTO Vis_ResR (R1, ResNo, Vis_ResR.Date, FrTm, ToTm) VALUES (@_R1,r.ResNo,@_Date,0,0)` – Ted James Jun 13 '15 at 21:33
@TedJames, just replace `VALUES` with your `SELECT` query with columns ordered like in the `INSERT`. Check updated answer with your data sample – Fabio Jun 13 '15 at 21:48
@Fabio thanks very much! The last sql code works perfect for me. – Ted James Jun 13 '15 at 21:49

SQL Simulating a foreach

1 Answers1