Updating Dynamics GP data directly in SQL


I would like to start by saying that I do not normally advocate updating Dynamics GP data directly in SQL Server. Most of the things you need to do should be handled either inside the Dynamics GP application or with integration tools like Integration Manager or eConnect. This is especially true of transactional data and anything to do with dates.

However there are some cases when you need to update master data that is isolated where the easiest way to do this is directly in the SQL table. Often this is due to the volume of data and the inability of tools like Integration Manager or eConnect to handle the task. Some obligatory warnings:

  • Always make a backup of your data before updating it
  • If you’re not 100% confident in what you’re doing, ask for help
  • Test first either in a test company, or on just 1 or 2 records if there is no test company available
  • If something you’re updating relies on other data that needs to be set up, verify all the necessary data exists (for example – if you’re updating Payment Terms for customers, make sure all the new Payment Terms are already set up in GP)

Now that we’ve gotten all the disclaimers and warnings out of the way, how do you actually do this? Something like a request to set the sales tax options for all inventory items to be ‘base on customers’ is pretty straightforward and I am not going to discuss that here. What is more complicated is the following scenario: update the Payment Terms for each customer based on a list in Excel. That’s a real life example that I’ve had to do more than once. You will have a list in Excel of each Customer ID and new Payment Terms that you want changed. How do you accomplish this?

One way to do this is to import the data from Excel into a new SQL table, then use that table in an update query. That’s certainly viable, but often creating new tables is a daunting process for users and sometimes importing data from Excel to SQL is not as easy as it looks. Also I am not crazy about cluttering up the database with this type of stuff, as often these tables are not deleted afterwards. My preferred approach is to create what I refer to as a ‘table on the fly’ inside of your SQL update query. Here is how I do this:

In your Excel file, create a new column that will turn the data into SQL code. For example, your Excel file will look something like this:

update-01

Add the following formula into column C (I put double quotes in red to separate them from the single quotes for visibility):

=select ‘&A2&‘ customer, ‘&B2&‘ terms union

Fill the formula down to all the rows of data in Excel, it should look like the following:

update-02

Now you can copy all the contents of that column into SQL, don’t forget to the remove the “union” from the last line. You can test it in SQL to make sure it will work:

update-03

As I mentioned before, you should validate your data to make sure that your new payment terms are already set up in GP. You can either do that inside the GP application itself, or check the SY03300 table, which is probably more efficient and will also check for any typos:

select * from
(
select 'AARONFIT0001' customer, 'Net 30' terms union
select 'ADAMPARK0001' customer, 'Net 30' terms union
select 'ADVANCED0001' customer, 'Net 15' terms union
select 'ADVANCED0002' customer, 'Net 30' terms union
select 'ALTONMAN0001' customer, '2% 10/Net 30' terms union
select 'AMERICAN0001' customer, 'Net 45' terms
) a
where terms not in (select PYMTRMID from SY03300)

Notice that I simply took what I copied into SQL from Excel and made it into a table by putting parentheses around it and adding an alias (a) at the end.

If all is well, the query above should come back with no results. If it shows results, you will need to set up the missing Payment Terms in GP first, before making the changes to the data.

Now you are ready to update your data. Here is my data from before the change:

update-04

Below is the query I used to update it. Again, please note that all I am doing is putting parentheses around the data from Excel and adding an alias (a) to it to make it behave as if it’s a table:

update RM00101
set PYMTRMID = a.terms
from RM00101 r,
(
select 'AARONFIT0001' customer, 'Net 30' terms union
select 'ADAMPARK0001' customer, 'Net 30' terms union
select 'ADVANCED0001' customer, 'Net 15' terms union
select 'ADVANCED0002' customer, 'Net 30' terms union
select 'ALTONMAN0001' customer, '2% 10/Net 30' terms union
select 'AMERICAN0001' customer, 'Net 45' terms
) a
where r.CUSTNMBR = a.customer

And here is my data after I run the query above:

update-05

Even though some of the customers’ terms did not change, it’s sometimes easier to run the update for all the data that you have rather than pick and choose.

Hopefully this example will help you in the future when you need to make updates to your data and the only choice is to do it directly in SQL.

Final warning – updating data directly in SQL Server can have some serious and unintended consequences. If you are not sure whether it’s ok to do or should be done this way, please ask for help.

10 Responses to “Updating Dynamics GP data directly in SQL”

  1. Great post Victoria,
    Users, heed the warnings Victoria gives, and especially the test company/environment. If you don’t have a test company available, STOP! and ask for help. Even the super SQL minions won’t run any script with update,delete, insert command without some form of testing first. I have SQL scripts that I have used for close to ten years that I still run in test company, just to make sure some weird has happened. It just takes seconds to confirm a SQL script’s results in a test company before you run the SQL script in production.

    Like

  2. Very good post Victoria. I use this method a lot and it’s very useful.

    If you validated all the necessary data, you can even write your formula to perform the update on each customer:

    =”update RM00101 set PYMTRMID = ‘”&A2&”‘ where CUSTNMBR = ‘”&B2&”‘”

    You need to be careful and know what you are doing but this is VERY quick to do. It will create multiple transactions, instead of single one as in your example, but that’s not really an issue.

    Like

  3. An option I normally use is write the update script then use word mail merge function (directory) and excel list to create large Update script one for each record to update….will have to try this!!

    Like

  4. for beginners a good thing to do is before the update statement use Begin transaction and after use Rollback transaction.
    using your eg.
    Begin Transaction
    update RM00101
    set PYMTRMID = a.terms
    from RM00101 r,
    (
    select ‘AARONFIT0001’ customer, ‘Net 30’ terms union
    select ‘ADAMPARK0001’ customer, ‘Net 30’ terms union
    select ‘ADVANCED0001’ customer, ‘Net 15’ terms union
    select ‘ADVANCED0002’ customer, ‘Net 30’ terms union
    select ‘ALTONMAN0001’ customer, ‘2% 10/Net 30’ terms union
    select ‘AMERICAN0001’ customer, ‘Net 45’ terms
    ) a
    where r.CUSTNMBR = a.customer
    Rollback transaction

    the result will tell you how many records will be updated. if the number of results is what you expect then highlight and run the query without the begin and rollback part. Just a confidence booster knowing how many records are going to be updated.

    Like

  5. Please, please, please backup your databases. I assume you would need to be a SQL Administrator to do this?

    I’m liking the use of Macros where possible. At least they will halt if something isn’t valid. In SQL it would be easy to add a field value that doesn’t exist.

    Like

  6. Good post Victoria, but I have become something of a convert to macros recently and am using them more for updating data where possible than direct SQL.

    Leslie (I think) has a post of putting your macro on steroids for speed which helps a lot.

    Like

    • Hi Ian,

      Thanks for the feedback. I am not a big fan of macros personally, have seen too many issues and have seen people spend a huge amount of time on troubleshooting them. Also have spent time helping fix data when they go wrong. 🙂

      -Victoria

      Like

  7. I use the table method for a customer who regularly updates their price lists (currency amount). The update requires changes to GP tables as well as a SalesPad custom price table. It works great – I just import the price template to a custom table in SQL, then run the scripts to update all the necessary tables in the company databases. What would take them hours or possibly days to update manually takes me about 15 minutes.

    Like

    • Hi Constance,

      I agree, for a process that you repeat over and over, it is often easier to use a table. Also, pricing changes are a little more complicated than the example I outlined here. 🙂

      -Victoria

      Like

  8. Brilliant and easy, as usual. Thanks Victoria.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: