• Replies 29 replies
  • Subscribers 339 subscribers
  • Views 500 views
  • Users 0 members are here
Related

Bulk-inactivating lists

Erin Murillas
$organization

Is there any way to inactivate multiple lists at once? we have thousands of old lists that haven't been generated in a decade or more, and while I don't think it will cause too many disruptions if I mass delete them, I'm wondering if mass inactivating is an option, without having to go into each list's properties and inactivate them one at a time. 

Parents
  • $organization
    Brian,
     
    Here is a ticketing example that shows the concepts.
     
    Before your update/delete statement, you add “Begin Transaction.” You can also name the transaction.  This will give you an opportunity to rollback your changes. However, if you commit the transaction, you cannot roll it back.
     
    In addition to wrapping the update/delete within a transaction, I also create a Table Variable and an Output clause. The advantage of this method is that it will display your changes before you commit/rollback the transaction.
     
    SSMS should natively work this way, but it does not. So far, I’ve avoided big mistakes using this method.
     
    /*
    Changing performance name within a production season query.
     
    Uses a table variable to view changes before committing transaction.
    nc
    */
    /*
    --Use this to make sure it'll change the correct performances before running update
    declare @Prod_Season_no int = 39385
    ;
    select
    i.description
    , convert(date,p.perf_dt) [Perf_dt]
    from T_INVENTORY i
    join T_PERF p on p.perf_no = i.inv_no
    where p.prod_season_no = @Prod_Season_no
    and i.description like @Perf_Description
    ;
    */
    declare @Prod_Season_no int = 39385
    , @Perf_Description varchar(30) = 'Vendor ZooBucks%'
    ;
     
    declare @Update_Perf_Text table (inv_no int, Old_description varchar(30), New_description varchar(30))
     
    begin transaction A
     
    update T_INVENTORY
    set description = 'Vendor ZooBucks'
    OUTPUT deleted.inv_no, deleted.description, inserted.description
    into @Update_Perf_Text
    --insert the changes into the table variable.
    from T_INVENTORY i
    join T_PERF p on p.perf_no = i.inv_no
    where p.prod_season_no = @Prod_Season_no
    and i.description like @Perf_Description
    ;
    select * from @Update_Perf_Text
    --show me the changes.
    ;
    /*
    If the changes are ok, then:
    COMMIT TRANSACTION A;
     
    If not ok, then:
    ROLLBACK TRANSACTION A;
    */
     
     
  • $organization in reply to Neil Cole

    Interesting! Thanks for the insight Neil :) That's super useful and I'll try to add TRANSACTION to some of my code. 

Reply Children
No Data