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:
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:
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:
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:
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:
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.