Monday, March 18, 2013

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.


Mike Lupro said...

Christina: I've used this method dozens of times and love it. I've even put the 'hard-coded' text into a cell and used an absolute reference to get it into the SQL statement. =+$A$1 where cell A1 has 'Update PM00200 set TXIDNMBR = '
and then add the concatenate row/column details about where the VENDORID OR TXIDNMBR variables are in worksheet. You can use Cell =+$A$2 to configure "Where other conditions need to be set.

Good post,

Christina Phillips said...

Glad you find it useful Mike! I get teased about it all the time by coworkers (and my blogmate). But sometimes the simplest solutions are just the quickest :)