How to Run SQL Server Query from PowerShell Script?

For a migration project, there was a requirement to read from SQL Server table data , process the migration and then insert / update the tables back from PowerShell. We got to run SQL Server query from PowerShell. Here is how to query SQL Server using PowerShell:

How to Run SQL Server Query from PowerShell Script

PowerShell to Run SQL Server Query

Function QuerySQLServer([string]$DBServer, [string]$DBName, [string]$Query)
{
 <#
  .SYNOPSIS
   Queries SQL Server from PowerShell
  .DESCRIPTION
  This PowerShell function Queries SQL Server from PowerShell
 .EXAMPLE
  QuerySQLServer "G1VWFE01" "MigrationDB" "SELECT [SourceSiteUrl], [TargetSiteUrl], [SiteType] FROM [MigrationData]"
  This example Gets table entries from the database "MigrationDB" in server "G1VWFE01"
 .INPUTS
  DBServer - Name of the Database Sever where the  target database is located
  DBName - Name of the Database from which the query to be executed
         Query - Query to Execute
 .OUTPUTS
  Rows from the predefined table "MigrationData"
 #>

 try
 {
     $ErrorActionPreference = "Stop"
  
     #Connection object
     $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=SSPI;Initial Catalog=$DBName")
     $cn.open() 

     #SQL Query to retrieve Table rows
     $cmd = new-object "System.Data.SqlClient.SqlCommand" ($Query , $cn)
     $reader = $cmd.ExecuteReader()
    
     #Process the Data
     while ($reader.Read()) 
     {
        #Iterate through Rows
        for ($i = 0; $i -lt $Reader.FieldCount; $i++) 
        {
            #Retrieve the Field (Column) values
            $Reader.GetValue($i)
        }
    }
 }
 catch
 { 
     #Write error message on screen and to a LOG file
     write-host $_.Exception.Message
     $_.Exception.Message >> "d:\error.log"
 }
 finally
 {
     $ErrorActionPreference = "Continue"
 }
}

#Call the function

QuerySQLServer "GIS-WFE01" "MigrationData"
     "SELECT [SourceSiteUrl], [TargetSiteUrl], [SiteType] FROM [MigrationSiteMapping]"

It’s also possible to process the Queried data like:

Function ProcessSQLServerData([string]$DBServer, [string]$DBName, [string]$Query)
{
 try
  {
  $ErrorActionPreference = "Stop"
  
  #Connection object
  $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=SSPI;Initial Catalog=$DBName")
  $cn.open() 

  #SQL Query to retrieve Table rows
  $cmd = new-object "System.Data.SqlClient.SqlCommand" ($Query , $cn)
  $reader = $cmd.ExecuteReader()
                #Process the Data
   while ($reader.Read()) 
    {
      for ($i = 0; $i -lt $Reader.FieldCount; $i++) 
      {
          #Retrieve the parameters from SQL Server "MigrationMapping" table
          $SourceSiteUrl = $Reader.GetValue(0)
          $TargetSiteUrl =$Reader.GetValue(1)
          $SiteType = $Reader.GetValue(2)

       #Check the SiteType
       if($SiteType  -eq "client")
       {
           Write-Host "Processing Client Site URL: $($SourceSiteUrl)"
           #call a PowerShell function from an External script to process the given parameters
           MigrateClientSite $SourceSiteUrl $TargetSiteUrl
       }
       else #it a Case site
       {
           Write-Host "Processing Case Site URL: $($SourceSiteUrl)"
           #call a PowerShell function from an External script to process the given parameters
           MigrateCaseSite $SourceSiteUrl $TargetSiteUrl
       }
      }
    }
   }
 catch
 { 
     #Write error message on screen and to a LOG file
     write-host $_.Exception.Message
     $_.Exception.Message >> "d:\error.log"
  }
  finally
  {
       $ErrorActionPreference = "Continue"
  }
} 

The above PowerShell SQL Server query example retrieves and process the SQL server table rows.

PowerShell script Execute SQL Server Query to Perform Insert/Update:

Similarly, for Insert and update, the PowerShell script goes like this:

Function RunSQLQuery([string]$DBServer, [string]$DBName, [string]$Query )
{
 <#
  .SYNOPSIS
   Executes SQL Query, such as Insert, Update, Delete, etc 
  .DESCRIPTION
   This PowerShell function executes the provided SQL query on the provided Database.
  .EXAMPLE
   powershell sql server query update: " RunSQLQuery "G1VWFE01" "MigrationData" "UPDATE [Table-name] SET [SiteStatus]='CREATED' WHERE [SiteID] = '$SiteID' AND [Category] = '$Category' "
                 powershell sql server query insert: use the SQL query as: "INSERT INTO [TableName] ( [SourceSiteUrl], [TargetSiteUrl],  [SiteType] ) VALUES ('https://sharepoint2007.crescent.com','https://sharepoint2010.crescent.com', 'case')"
  .INPUTS
   DBServer - Name of the Database Sever where the "LVMAdmin" database is located
   DBName - NameNo value of the cases table
   Query - Query to execute. Such as Insert, Delete
  .OUTPUTS
   None
 #>
 try
 {
  #Connection object
  $cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$DBServer;Integrated Security=SSPI;Initial Catalog=$DBName")
  $cn.open() 

  $cmd = new-object "System.Data.SqlClient.SqlCommand" ($Query, $cn)
  $cmd.ExecuteNonQuery()
 }
 catch
        {
               #Write error message on screen and to a LOG file
               write-host $_.Exception.Message
               $_.Exception.Message >> "d:\error.log"
        }
        finally
        {
            $ErrorActionPreference = "Continue"
        }
}

This will run SQL Server query from PowerShell.

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

Leave a Reply

Your email address will not be published. Required fields are marked *