CODEWITHSUNDEEP
sqlloopscursoradvantage-database-server
Jay
Jay

Reputation: 5

Looping through rows of a table using each row as Insert Into - SQL

I have a table called Reviews, and I don't think the contents of this matter too much for the question, but I want to go through each row of this table, one by one, using the data in each row as part of an Insert Into statement. As part of each row being used, I need to increment an integer (called Evid) by 1 because that integer needs to be used as part of the Insert Into statement. So the first row will have 1, the second row will have 2 etc.

I am using Advantage Architect, and a rough idea of how I see this working is below, which I know I need help on.

Row example of the table I want to use to loop through:

IDENT|REFERENCE|TITLE
1234 | TEST    | testing 
4456 | TEST2   | testing2

And I want to insert into another table, where it'll look like this. Each row it runs through from the above will be used to insert more than one row into the below table, but keeping the EVID as the sequential INTEGER value.

IDENT|REFERENCE|TITLE    |STAFF |EVID
1234 | TEST    | testing |STAFF1| 1
1234 | TEST    | testing |STAFF2| 1
4456 | TEST2   | testing2|STAFF1| 2
4456 | TEST2   | testing2|STAFF2| 2

_ 

declare evid integer;
declare cur cursor;

evid = 1;

open cur

//Do Insert Into
//Evid +1;
Close cur;

Upvotes: 0

Views: 2615

Answers (1)

Alex W
Alex W

Reputation: 3373

The relevant information on using cursor can be found in online help

Modifying the sample code for your problem:

DECLARE @evid Integer;
DECLARE cur CURSOR;    

OPEN cur As SELECT * FROM sourceTable;

@evid = 1;


WHILE FETCH cur DO
    INSERT INTO destination (IDENT, REFERENCE, TITLE, STAFF, EVID)
        VALUES (cur.IDENT, cur.REFERENCE, cur.TITLE, 'STAFF1', @evid);
    INSERT INTO destination (IDENT, REFERENCE, TITLE, STAFF, EVID)
        VALUES (cur.IDENT, cur.REFERENCE, cur.TITLE, 'STAFF2', @evid);

    @evid = @evid + 1;
END WHILE;


CLOSE cursor1;

Upvotes: 1

Related Questions