The Swamp

PowerShell, Just Bring It: Looping Through a DataTable – #TSQL2sDay

Print Friendly

Thank you to Wayne Sheffield (blog @DBAWayne) not only for hosting February’s T-SQL Tuesday #39, but for bringing The Rock into it. Wayne is writing a series about PowerShell and asked for others in the community to participate with a post about the most electrifying ways to use PowerShell with SQL Server.

Looping through a DataTable in PowerShell

How do you handle ad-hoc data management when you need to access two or more SQL Server boxes that aren’t set up or able to communicate directly?  Let’s say, for example, you need to read from the Wrestlers database on ServerA and use that data to make changes on ServerB’s Actors database. Writing a .NET console application to do basic data manipulation is overkill. However, if you have a solid .NET background and don’t really want to lay the smackdown on PowerShell, you can read one of my previous posts on how to essentially embed and run .NET code within a PowerShell script.

Now if you are one of the millions, and millions, of PowerShell fans, let me show you how you can tag team these servers and accomplish the same thing ‘the PowerShell way’:

#Create your SQL connection string, and then a connection to Wrestlers
$ServerAConnectionString = "Data Source=ServerA;Initial Catalog=Wrestlers;User Id=TheRock;PWD=PeoplesChamp"
$ServerAConnection = new-object system.data.SqlClient.SqlConnection($ServerAConnectionString);

#Create a Dataset to hold the DataTable from Wrestlers
$dataSet = new-object "System.Data.DataSet" "WrestlersDataset"
$query = "SET NOCOUNT ON;"
$query = $query + "SELECT WrestlerName, BeltsWon "
$query = $query + "FROM   dbo.ProWrestlers "
$query = $query + "WHERE  BeltsWon > 0; "

#Create a DataAdapter which you'll use to populate the DataSet with the results
$dataAdapter = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $ServerAConnection)
$dataAdapter.Fill($dataSet) | Out-Null

#Close the connection as soon as you are done with it
$ServerAConnection.Close()
$dataTable = new-object "System.Data.DataTable" "FormerChamps"
$dataTable = $dataSet.Tables[0]

$ServerBConnectionString = "Data Source=ServerB;Initial Catalog=Actors;User Id=TheRock;PWD=PeoplesChamp"
$ServerBConnection = new-object system.data.SqlClient.SqlConnection($ServerBConnectionString);
$ServerBConnection.Open()

#For every wrestler that has won a belt, update their Actor record to show that they are a FormerChampion
$dataTable | FOREACH-OBJECT {
  $cmd = new-object System.Data.SQLClient.SQLCommand
  $cmd.CommandText = "UPDATE dbo.MovieStars SET FormerChampion = 1 WHERE ActorName = @WrestlerName"
  $cmd.Connection = $ServerBConnection
  $cmd.Parameters.AddWithValue("@WrestlerName", $_.WrestlerName) | Out-Null
  $rowsUpdated = $cmd.ExecuteNonQuery()

  #write-host "WrestlerName: " $_.WrestlerName
}

$ServerBConnection.Close()

For those of you that are still with me, and have never been fans of The Rock, or professional wrestling…you don’t know what you’re missing.

If you enjoyed this, please share with others
    The following two tabs change content below.
    As an independent consultant with 18 years of experience, clients bring me in when they need a seasoned database professional; to first understand their business problem, and then provide the best data storage solution possible.
    Loading Facebook Comments ...

    1 Comment

    1. [...] Krueger (@DataOgre) http://www.dataogre.com/2013/02/12/powershell-just-bring-it-tsql2sday/ shows us how to use PowerShell to establish connections between multiple SQL Servers and loop [...]

    Leave a comment

    No Trackbacks.