Updating Records with Excel

Recently, I have had to update 1099 information for a set of vendors that was inadvertently cleared out.  In that case, I retreated back to one of the simplest ways that I approach updating groups of records using Excel.  In this case, I had a backup of the information, so I first ran the following script to pull the information I needed to restore:

--select vendor 1099 information
select VENDORID, TEN99BOXNUMBER, TEN99TYPE, TXIDNMBR from PM00200   Now, of course, you could put a where clause on this if you like, to further restrict the information.  Then when I have my results, I copy/paste them in to Excel like this...    

  Next, I add a column to the right and create a CONCATENATE formula to build my SQL script.  Yes, yes, yes, I know this is borderline silly...but it is quick, easy, and allows you to store (with accuracy) what you changed and how you determined it.  Here is my concatenate formula that combines static text like UPDATE PM00200 with the different fields in the spreadsheet:   =CONCATENATE("UPDATE PM00200 SET TEN99BOXNUMBER=",B2,","," TEN99TYPE=",C2,",","TXIDNMBR='",D2,"' WHERE VENDORID='",A2,"'")   The result is a column of SQL statements to perform my update:  



Note the complete SQL script that is a result of the CONCATENATE formula I created.  I then cut/paste the column of scripts in to SQL Server Management Studio to run my update.  And then I save the spreadsheet as a record of what I updated and the source data I used.  Hope this is a helpful little trick for others of you who need to update data and want to do so in a controlled way.

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.