SQL while loop with select query

Question

I'm trying to accomplish the following thing: I receive a DocumentID. Find all the records in a table that match the specific DocumentID, for example I have 10 records matching and every record is with different DocumentAttachmentID. I update all the records with the new data. The problem comes that I need to insert some of the information from these ten records + other information received to a new table, which is History table, i.e. I need to insert ten new records there.

I've succeeded to this with Cursor, but it looks like that the Cursor isn't really good, because of the performance.

Is there a way to loop throught the 10 records that I selected from this table and for every record to take some information, add some additional info and then insert this in the other table ?

EDIT: I tried to do this withoud looping(thanks you all for the answers) I will try it tomorrow, do you think this is gonna work ?:

With the first Update, I update all documentAttachments, The second block is INSERT TO, which should insert all document attachments in the other table with some extra columns.

UPDATE [sDocumentManagement].[tDocumentAttachments]
         SET DeletedBy = @ChangedBy,
             DeletedOn = @CurrentDateTime,
             IsDeleted = 1,
         WHERE DocumentID = @DocumentID;

         INSERT INTO [sDocumentManagement].[tDocumentHistory] ( DocumentAttachmentID, DocumentID, ActivityCodeID, ChangedOn, ChangedBy, AdditionalInformation )
         SELECT DocumentAttachmentID,
                @DocumentID, [sCore].[GetActivityCodeIDByName] ( 'DeletedDocument' ),
                @CurrentDateTime,
                @ChangedBy,
                @AdditionalInformation
         FROM [sDocumentManagement].[tDocumentAttachments]
         WHERE DocumentID = @DocumentID;

This doesn't need a loop... but some sample data and expected output would be helpful here... other wise it's all pseudo code — S3S, Aug 10 '17 at 18:57
Can you provide the SQL create statements for the two tables? I can then provide the SQL for the insert join. — mikelus, Aug 10 '17 at 19:03
you can find resolution here https://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursorhttps://stackoverflow.com/questions/61967/is-there-a-way-to-loop-through-a-table-variable-in-tsql-without-using-a-cursor — Yuri, Aug 10 '17 at 19:13
thank you guys, I edited my post with the set based insert I made. Do you think this will work ? I can't test it today(tomorrow probably), because do not have access to the database, but it should work ? — VBORISOV, Aug 10 '17 at 20:25

score 2 · Answer 1 · answered Aug 10 '17 at 19:05

for looping without a cursor I quite often use the following technique:

DECLARE @items TABLE(id INT, val INT);

DECLARE @id INT;
DECLARE @val INT;

WHILE EXISTS(SELECT * FROM @items) BEGIN
    SELECT TOP(1) @id = id, @val = val FROM @items;
    DELETE FROM @items WHERE (id = @id);

    --do what is needed with the values here.
    SELECT @id, @val;
END

this treats the @items table as a queue pulling the rows off one at a time till it is empty.

A loop is contraindicated for this type of thing. – HLGEM Aug 10 '17 at 19:23 — HLGEM, Aug 10 '17 at 19:23

SQL while loop with select query

1 Answers1