January 17, 2017 
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.
Victoria Yudin 
Hi Victoia,
You commented “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.” You can add
AND r.PYMTRMID <> a.terms
to your where clause and it will only update items where the terms are different.
John
LikeLike
John,
Good point, thanks. I would be very curious what the performance difference is with updating everything vs. checking what to update. I suspect for anything but huge data sets it won’t be anything substantial.
-Victoria
LikeLike
Hi Victoria- Do u happen to have a SQL script for adding new inventory items to GP?
Thank you.
LikeLike
Hi Mario,
I would strongly advise you AGAINST adding inventory items directly in SQL. There is a lot of data validation that would need to happen when items are created. There are also a number of related tables that get updated, so this is better done either directly in the Dynamics GP user interface or with tools created specifically for this, like Integration Manager or eConnect.
-Victoria
LikeLike
Thank you Victoria for your advice. I used IM and it worked seamlessly.
LikeLike
Hi Victoria…. Im trying to add vendors in GP (V.9) but come up with the following error: “This record has been created since your attempt to create it. Changes wont be saved”… how would I get past this. I can add vendors in SQL but this is not ideal We want to be able to do this within GP…..
Thanks
Colin
LikeLike
Hi Colin,
I have seen this happen when you are using Master Triggers from Professional Services Tools Library and have not set up the new company in there or you have other/custom triggers on your PM tables in SQL. Hope that helps.
-Victoria
LikeLike
Thanks Victoria
LikeLike
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.
LikeLike
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.
LikeLike
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!!
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
I remember years ago at Convergence a Microsoft Engineer advised against using macros to import data. Do you happen to know if there was ever a formal release or KB article about the subject?
LikeLike
Hi Jeremy,
I am not aware of any formal documentation stating this. For what it’s worth, I am also not crazy about macros. When they work they are fine, but they break so often it’s often not worth the trouble.
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
Brilliant and easy, as usual. Thanks Victoria.
LikeLike