Thursday, November 01, 2007

Precision Computing - Interacting with SQL & PowerShell

Yet another way to ETL without Integration Services? 

Jeffrey McManus recently wrote about database queries with PowerShell – a small script that lets you query a SQL data store. This is really powerful. Rather than context switch into SQL Express (or TOAD, or your other favourite administration tool,) you can do what you need from PowerShell.

It goes even further, though. A lot of PowerShell's built-in commands have a set-oriented flavour: Where-Object, Select-Object, Group-Object, and Sort-Object. I also blogged about a set intersection script here: http://www.leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx. After spending a ton of time in Oracle databases during an internship at General Electric, I remember always wishing that DIR supported a WHERE clause. Well, with PowerShell, it does!

One thing Jeffrey's post doesn't fully highlight is that the .NET Framework can also return fully structured objects that represent the results of your query. If your SELECT query returns a recordset with Name and Address columns, you can get back objects with Name and Address properties. With that, you can slice and dice the results even further in the shell.

PS D:\Temp> Invoke-SqlCommand.ps1 -Sql "SELECT TOP 5 * FROM Orders" | Format-Table

    OrderID CustomerID   EmployeeID OrderDate   RequiredDat ShippedDate     ShipVia     Freight
                                                e
    ------- ----------   ---------- ---------   ----------- -----------     -------     -------
      10248 VINET                 5 7/4/1996... 8/1/1996... 7/16/199...           3       32.38
      10249 TOMSP                 6 7/5/1996... 8/16/199... 7/10/199...           1       11.61
      10250 HANAR                 4 7/8/1996... 8/5/1996... 7/12/199...           2       65.83
      10251 VICTE                 3 7/8/1996... 8/5/1996... 7/15/199...           1       41.34
      10252 SUPRD                 4 7/9/1996... 8/6/1996... 7/11/199...           2        51.3

Precision Computing

1 comment:

adam said...

Hi, this is not so related to your page, but it is the site you asked me 1 month ago about the abs diet. I tried it, worked well. Well here is the site