Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Unanswered: Do I need a cursor or not to loop this?

    Good DB folk,
    A generic question please:

    If I wish to act on a multi-row AFTER INSERT operation, do I need a loop such
    as a cursor, if I want to touch each record?
    Or, is there some other accepted scheme for a SET based query?

    Thanks,
    Rich

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,858
    Provided Answers: 17
    For an insert operation, you can use the OUTPUT clause to capture the inserted records (including IDENTITY values) in a temp table. It really depends what sort of things you need to do to the result set.

  3. #3
    Join Date
    Oct 2015
    Posts
    2

    triggered

    Quote Originally Posted by MCrowley View Post
    For an insert operation, you can use the OUTPUT clause to capture the inserted records (including IDENTITY values) in a temp table. It really depends what sort of things you need to do to the result set.
    ok, that's a new command for me.
    Looks pretty cool though.

    I'm needing to look at a specific (inserted) column, for a specific string: then act on that string & potentially write
    it out to a column in that same table.
    These are largely multi-row INSERTs I'm dealing with, which is why I thought perhaps a CURSOR was needed..
    Is OUTPUT what I'm looking for in this case?
    Thanks,
    Rich

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,858
    Provided Answers: 17
    Probably. It will partially depend on what sort of processing would be needed after the insert:
    Code:
    -- Set up tables
    create table test1
    (col1 int,
     col2 varchar(20))
    
    create table #temp1
    (col1 int,
     col2 varchar(20))
    
    -- Run insert
    insert into test1 (col1, col2)
    output inserted.col1, inserted.col2 into #temp1 (col1, col2)
    values (1, 'hello'),
    	(2, 'good-bye'),
    	(3, 'hi')
    
    -- process only records with even col1 values
    select *
    from #temp1
    where col1 % 2 = 0
    
    select *
    from test1
    go
    -- cleanup
    --drop table #temp1
    --drop table test1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •