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
– S3S, Commented Aug 10, 2017 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
– mikelus, Commented Aug 10, 2017 at 19:03
you can find resolution here stackoverflow.com/questions/61967/… — Yuri
– Yuri, Commented Aug 10, 2017 at 19:13

David · Accepted Answer · 2017-08-10 19:05:56Z

2

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.

answered Aug 10, 2017 at 19:05

David

3,7732 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HLGEM Over a year ago

A loop is contraindicated for this type of thing.

Collectives™ on Stack Overflow

SQL while loop with select query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related