• Replies 3 replies
  • Subscribers 701 subscribers
  • Views 152 views
  • Users 0 members are here
Options
Related

SQL for bulk updating default sales start date

Kanani Reichlin
$organization

Hey Y'all,

I need to bulk update our default sales start date on this season's performances.  I know I had this bit of code but I seem to have lost where I saved it.  Is anyone here willing to share there's before I attempt to recreate it?

Thanks,

Kanani Reichlin

kananir@sct.org

p.s. can't wait for this to be added to season maintenance!

Parents
  • $organization
    Here you go. It uses a table variable, output clause, and transaction to display the changes before you commit it (can also rollback before committing). This really should be how SSMS operates, but it doesn’t. Update 1546 rows, which 1546 rows? Using a table variable and output can show you.
     
    As always, do this in your Test environment as a proof of concept before attempting in Prod.
     
    /*
    Bulk changing Default Start Sale Date
     
    Uses a Table Variable and Output to display changes before committing transaction.
     
    Neil Cole
    MN Zoo
    5/10/2022
    */
     
    DECLARE @Prod_Season_no INT = 24228
    , @Start_Perf_dt DATE = '2019-07-02'
    , @End_Perf_dt DATE = '2019-07-20'
    , @NEW_def_start_sale_dt DATETIME = '2018-06-01 08:00 AM'
    ;
    DECLARE @Change_def_start_sale_dt TABLE
    (perf_no INT, prod_season_no INT, perf_code VARCHAR(10), perf_dt DATETIME, OLD_def_start_sale_dt DATETIME, NEW_def_start_sale_dt DATETIME)
    ;
     
    BEGIN TRANSACTION A
     
    UPDATE T_PERF
    SET def_start_sale_dt = @NEW_def_start_sale_dt
    OUTPUT DELETED.perf_no, DELETED.prod_season_no, DELETED.perf_code, DELETED.perf_dt, DELETED.def_start_sale_dt, INSERTED.def_start_sale_dt
    INTO @Change_def_start_sale_dt
    WHERE T_PERF.prod_season_no = @Prod_Season_no
    AND CONVERT(DATE,T_PERF.perf_dt) BETWEEN @Start_Perf_dt AND @End_Perf_dt
    ;
    SELECT * FROM @Change_def_start_sale_dt
    ;
    /*
    If ok, then
    COMMIT TRANSACTION A;
     
    IF NOT ok, then
    ROLLBACK TRANSACTION A;
    */
     
     
    Neil
     
Reply
  • $organization
    Here you go. It uses a table variable, output clause, and transaction to display the changes before you commit it (can also rollback before committing). This really should be how SSMS operates, but it doesn’t. Update 1546 rows, which 1546 rows? Using a table variable and output can show you.
     
    As always, do this in your Test environment as a proof of concept before attempting in Prod.
     
    /*
    Bulk changing Default Start Sale Date
     
    Uses a Table Variable and Output to display changes before committing transaction.
     
    Neil Cole
    MN Zoo
    5/10/2022
    */
     
    DECLARE @Prod_Season_no INT = 24228
    , @Start_Perf_dt DATE = '2019-07-02'
    , @End_Perf_dt DATE = '2019-07-20'
    , @NEW_def_start_sale_dt DATETIME = '2018-06-01 08:00 AM'
    ;
    DECLARE @Change_def_start_sale_dt TABLE
    (perf_no INT, prod_season_no INT, perf_code VARCHAR(10), perf_dt DATETIME, OLD_def_start_sale_dt DATETIME, NEW_def_start_sale_dt DATETIME)
    ;
     
    BEGIN TRANSACTION A
     
    UPDATE T_PERF
    SET def_start_sale_dt = @NEW_def_start_sale_dt
    OUTPUT DELETED.perf_no, DELETED.prod_season_no, DELETED.perf_code, DELETED.perf_dt, DELETED.def_start_sale_dt, INSERTED.def_start_sale_dt
    INTO @Change_def_start_sale_dt
    WHERE T_PERF.prod_season_no = @Prod_Season_no
    AND CONVERT(DATE,T_PERF.perf_dt) BETWEEN @Start_Perf_dt AND @End_Perf_dt
    ;
    SELECT * FROM @Change_def_start_sale_dt
    ;
    /*
    If ok, then
    COMMIT TRANSACTION A;
     
    IF NOT ok, then
    ROLLBACK TRANSACTION A;
    */
     
     
    Neil
     
Children