Using T-SQL to Automatically Update M2M Tables

One of the primary values a good DBA brings a company is in automating tedious, time-consuming tasks. In my last article, I discussed the problems when adding a sales person to M2M. M2M’s standard procedure is to create a new sales person and manually re-assign every customer (over 700 of them) to the new sales person.

In this article I’ll demonstrate a better way to do so. Before reading on, please read my standard disclaimer. Also, please note that M2M does not recommend updating the M2M database outside of the M2M program, and will not assist you if you make a mistake, at least not without charging you an hourly rate to do so.

The Basic Process

  1. Always develop the script on a test server.
  2. Before applying the script to the live server, always make a full backup of the database.
  3. Create a select statement to verify that you are about to update the correct subset of data.
  4. Make a backup copy of the table. Yes, I know you already made a backup of the entire database, but we need to make a separate backup of the table and keep it for reference for a few weeks.
  5. Run any applicable reports which may be effected by the data change. In all of my years in M2M customization, I am still surprised by how quirky the database is and how seemingly insignificant changes can cause big problems.
  6. Craft and run your update statement. In M2M you have to be particularly careful because the entire database lacks referential integrity so you can store nearly anything in most fields. It’s very easy to make a mistake. Make sure that the number of records updated equals the number in your original select statement.
  7. Set a reminder to delete your backup table after you’re sure that no problems will arise.

Update the Sales Person Table

Fire up your query tool of choice. I prefer TOAD for SQL Server. In this example, we are reassigning someone with the initials ZZZ with AAA. Following the steps above craft the Select statement like so:

Use M2MData01
Select SLC.fcompany, SLC.fcustno, SLC.fstate From slcdpm SLC where fsalespn = 'ZZZ'

You should be presented with a grid of records which represent customers assigned to sales person ZZZ. Check the grid to make sure that these are the records you wish to change.

Next make a backup of the table with this statement.

select * into slcdpm_Backup
from slcdpm

This creates a copy which we will retain for a few weeks. Next run the Sales Commissions Report (RPCMSN) and export it for comparison.

The update statement would look like the following.

update slcdpm set fsalespn = 'AAA'
where fsalespn = 'ZZZ'

Note the number of rows effected. Be sure that it equals the number of rows returned in step 3.

Re-run the select statement above with the new initials to check your work.

Select SLC.fcompany, SLC.fcustno, SLC.fstate From slcdpm SLC where fsalespn = 'AAA'

Finally, re-run the Sales Commissions report and export it. You can then compare it to the first export to verify there were no changes.

In fact, if you export it as a Comma Delimited file you can use TOAD to file compare both and save yourself a lot of time. While this was a simple example, nearly all of my automatic updates are done this way.

Any questions?

8 comments to Using T-SQL to Automatically Update M2M Tables

  • David

    Are you able to roll Toad reports back into M2M?

    Thank you

  • Kim

    Thanks for the article but how would I use the backup table to fix a mistake?

  • Mark, I don’t use TOAD for reports exactly, I use it to write SQL Queries and then I use other software to present the reports such as SQL Server Reporting Services or Crystal Reports. I intend to follow up with some articles about TOAD. In fact, the next article will touch on it.

    Kim, I’ll follow up with how to recover from the problem in the next article. 🙂

  • Thanks for the article. Step by step examples are great!

  • Thanks Avi, I agree. One of my personal pet peeves is when you find articles from a Google search when you have a problem and the explanation isn’t thorough enough to actually help you. The answer is there if you knew enough to implement it, but if you knew enough you wouldn’t have to Google for the answer anyway.

  • John Sitka

    You mention to check that the Update statement affects the same number of rows as the select statement.
    The update statement should have been preceeded by a Begin Transaction statement

    After the row count has been confirmed, and the results checked then issue the Commit Transaction statement. If a mistake has been made use the Rollback Transaction statement.

    This is a general pattern for all adhoc data manipulation queries issued against production data. The transaction statements are local per connection so be sure to understand the connectivity of your query tool.

  • John, thanks so much for your comment. One of the primary reasons I blog is for suggestions like that.

    While I do agree with you, I chose not to include transactions in this for two reasons.

    1. While a transaction is in progress you effectively lock the table and will cause a M2M Red Box ADOBC error because M2M cannot access the Customer Table. I had introduced Transactions in a previous article and then had frantic emails because M2M Admins are by their very nature “accidental DBA’s”, wear many different hats, etc. They kept forgetting that you cannot leave a transaction open like that very any amount of time.
    2. When I googled for information on this topic almost none of the other websites included transactions when updating SQL tables and I thought I would follow suit for readability reasons.

    I do extensively use transactions in update statements in my day to day work, but I don’t check the results manually. I would change the above statement to something like this:

    UPDATE slcdpm SET fsalespn = ‘AAA’
    WHERE fsalespn = ‘ZZZ’

    IF @@ROWCOUNT = 700 — (The correct number of updated records)
    PRINT ‘Wrong Number of Records, Update Aborted.’

    That way the transaction is immediate and you protect yourself from making a mistake. Again, I kept this simple for the target audience.

  • I would use transactions and I would do a bit of sampling (more than just # of rows affected) to ensure the data updated the one you wanted updated, then auto commit the tran, otherwise rollback.

    Also, as a caveat to #1, test with a like sized DB and table to anticipate how long it should take.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>