PowerShell and SQL - Getting Data in and out

This week we are going to look at interacting with SQL server using some simple DML commands. Using PowerShell you can perform select, insert, update and delete actions on your SQL Server. There are two functions from the SQLPSX library that we will be using today: Get-SqlData and Set-SqlData.

Note: I am going to be using the SQLPSX modules again. If you don't have them installed go HERE

Getting Data out of SQL Server

First lets look at how to execute a simple SELECT statement against a database. I am going to be using Microsoft's sample database Adventure Works on a SQL 2008 R2 install. The function we are going to be using is Get-SqlData. First thing we want to do is look at the help file to see what it can do.

Get-Help Get-SqlData

On my computer i get this result:

This command has three parameters, two required (dbname and qry) and one optional (sqlserver). If you leave out the sqlserver parameter it will default to localhost. 

NOTE: I am assuming that you have the proper access to the SQL server with your current logged in Windows account. If you do not look at example two when you run the command 'Get-Help Get-SqlData -examples'

Lets grab the titles and names from the person.person table where the first name is John

NOTE:  In the examples in this post I am using PowerShell ISE and typing everything in the script pane first before executing it. 

Import-Module sqlmaint

$sqlserver = 'Nightwing'

$dbname = 'AdventureWorks2008R2'

$qry = "select title, firstname, middlename, lastname

from person.Person

where FirstName = 'John'"

Get-SqlData -sqlserver $sqlserver -dbname $dbname -qry $qry

Here is my result:

Now let's break the script down. First I imported the sqlmaint module. Then I declared three variables ($sqlserver, $dbname, $qry) which I assigned the string literals for my server, the database and my query. Then I ran the Get-SqlData function and used those variables as the input parameters. Why did I do it this way instead of feeding the strings directly to the function? That is an excellent question. I did it for three reasons. 

First is readability, that is a pretty simple query but a more complicated one would be harder to debug in-line. 

Second is for re-usability, when you write a script to interact with SQL server you are probably going to execute more than one query. This way I set the server name and database name once and on every subsequent database call I just need to redefine the $qry variable. 

Lastly, I did it for portability. I can move this script from one server to another and all I have to do is change the $sqlserver variable once and it will work. I don't have to hunt through the code or do a find and replace. This is very helpful when you, as you should be, are testing your code on a development server before moving it to production.

Putting Data Back into SQL Server

Ok, so now we know there are 58 people in the person.person table with the first name of John. Lets write a query to change the last name of one of those guys from Beaver to Rutherford. The primary key on the person table is the column BusinessEntityID. I checked and Beaver's ID is 429. For this we are going to use the Set-SqlData function.

Here are the results for a 'Get-Help Set-SqlData':

If you haven't closed PowerShell ISE then you don't need to re-import the sqlmaint module and you don't have to re-declare the sqlserver and dbname variables. Just open a new script window and use the following.


$qry = "update person.Person
set lastname = 'Rutherford'
where businessentityid = '429'"

Set-SqlData -sqlserver $sqlserver -dbname $dbname -qry $qry

Notice when you ran it, you didn't get any thing back in the window. That's because Set-SqlData does not return a result set. Check out the synopsis for each of the commands in the Get-Help results to see the difference.

We can verify our change with another select statement. Open a new script window and type in the following.

$qry = "select title, firstname, middlename, lastname
from person.Person
where businessentityid = '429'"

Get-SqlData -sqlserver $sqlserver -dbname $dbname -qry $qry

Which returns:

Sum Up

Now you are probably saying to yourself that you could have done all this in SSMS. While that is true, next week I'll show you how to use this new knowledge in conjunction with the last post 'Get Server and DB Information' to gather information about your servers and databases and store it in a database.
I will be giving a presentation on this subject here in Austin at our local PASS group CACTUSS next week. Stop by and check it out.

Hasta Luego,
RutherfordTX