SQL Updates Made Easy Using Excel

Have you ever setup several inventory items in Dynamics GP and realized after the fact that you have assigned the incorrect item type? Once you’ve setup an item in GP, with the exception of the “Discontinued” option, the system will not allow you to change the item type within the application. Your options are to either setup a brand new item or update the item type field in the IV00101 table using a SQL script. The SQL script is the simplest solution and the most efficient if you have only one item to change. However, if you have multiple items to change, you don’t want to have to modify and run a script for every single item. Here’s an easy way to use Excel to do the work for you.

Note:  In this example we will assume that the items were setup incorrectly as Service item types and should be Sales Inventory type items. Also, it is very important to make sure these items do NOT currently exist on any un-posted SOP or POP transactions prior to running these scripts.

1.   See below to create a SmartList for a group of items with an Item Type of Service all beginning with “A-”:

2. Delete row 1 and the columns for Item Description and Item Type. Then insert a blank column before Item Number.

3. In column A type in the following: Update IV00101 set ITEMTYPE = ‘1’  WHERE ITEMNMBR = ‘ and copy the partial statement down to all rows for each item.

4. In column C type in a double quote in row 1 and copy down to all rows for each item.

5. Then place your cursor in Column D, and select the fx button to select a function. Select the Concatenate function and click OK.

6. Enter the cells A1, B1, and C1 as your function arguments as below, then click OK.

7. This formula will combine and populate the information from column A, B, and C into Column D. Click on cell D1 to form a box around the window.  Then click on the bottom right corner of the cell and drag the box down to all rows to copy down the formula for each item.  The script will repeat for each individual item.

8. Then copy and paste all of the contents of Column D into your Query window in SQL Server Management Studio. Click Execute to run all the scripts at once.
   Note:  Make sure you backup your company database prior to running any SQL scripts.

This is just a small group of items, but imagine if you had hundreds of records to change. This is a very simple example of how you can use Excel to make your SQL scripting a little easier. The same logic can also be applied for updating other master records or transactions.

RSM offers access to Certified Microsoft Professionals, help desk and phone support, knowledge and experience with third party products and dedicated account management.  To learn more about who we are go to:  http://rsmus.com/who-we-are.html or contact our professionals at erp@rsmus.com or by phone at 855.437.7202.

By: Nancy Hogan

Nancy Hogan