While I am a big advocate of making changes to data via the front end or using an officially supported integration tool, there are times when a SQL script is the quicker way of making a small update.
I recently needed to update the purchasing unit of measure on 60,000 inventory items. The below SQL script was created to make this update:
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
UPDATE
['Inventory Item Master']
SET
['Inventory Item Master'].PRCHSUOM = ['Inventory U of M Schedule Detail Setup'].UOFM
FROM
IV00101 AS ['Inventory Item Master'] --Item Master (IV00101)
INNER JOIN
IV40202 AS[/sqlgrey] ['Inventory U of M Schedule Detail Setup'] --Inventory U of M Schedule Detail Setup (IV40202)
ON [/sqlgrey]
['Inventory U of M Schedule Detail Setup'].UOMSCHDL = ['Inventory Item Master'].UOMSCHDL
This script was suitable for the client’s data against which it was run, but may not be suitable for all data configurations.
As always before running a script, make sure you understand what the script does, test it on a test company/system and have a good backup before running on live and verify the results.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.