Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: Foreach

For this demo, you will require SQL Server with the AdventureWorks database and a copy of Windows Powershell. We intend to build up a number of UPDATE statements based on data within AdventureWorks.HumanResources.Employee. We want to give all 157 production technicians an extra ten vacation hours, but need an easy way of generating the many lines of SQL we require. First of all, we need to run the following query to return the employeeID of all employees who meet this criteria:-

USE AdventureWorks
FROM HumanResources.Employee
WHERE Title LIKE 'Production Technician%'

With a fresh installation of the AdventureWorks database, this should return 157 results. Create a file on the root of C: called myfile.txt and copy and paste in the results using notepad or other text editor. You should have one EmployeeID per line.

Now we can build up our UPDATE queries by using Windows Powershell. Open Powershell and enter the following:-

get-content c:\myfile.txt | Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"} | out-file c:\output.txt

If you now check the newly created file (ouput.txt) on C: drive, you should have 157 UPDATE statements which you can copy and paste into SQL Server. Executing will give the production assistants their extra hours.


Now to explain the powershell code…..

get-content c:\myfile.txt

simply reads the contents of our file. If you solely run this part of the command in Powershell, the contents of the file will be displayed on screen.

The main part of our command is the foreach loop which builds our SQL statements by adding our text before and after the contents of each row. The contents of each row is held in “$_

Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"}

The final part of our code simply writes the changes out to our output.txt file

out-file c:\output.txt

There are much more elegant ways of writing our SQL statement. We could of course, have written a single UPDATE statement and included,

WHERE Title LIKE 'Production Technician%'

…. but that would not have been as much fun and you would not have just learnt this handy Powershell tip!!


Tags: , , , , , , ,

%d bloggers like this: