PowerShell is a powerful scripting language that can be used to automate tasks and perform various operations. One of the interesting use cases of PowerShell is connecting to SQL Server and running queries. In this article, we will explore how to achieve this using SQL Server Native Client.

First, let’s create a sample database and a table that we will use for our PowerShell script:

CREATE DATABASE SQLAuthority
GO
USE SQLAuthority
GO
CREATE TABLE EmployeeMaster (
EmployeeID INT,
FirstName VARCHAR(10),
LastName VARCHAR(10)
)
GO

Now, let’s dive into the step-by-step process of using PowerShell to insert data into the EmployeeMaster table:

# Make Connecting string to the SQL Server Instance and the Database
$con = New-Object System.Data.SqlClient.SqlConnection("Data Source=.; Initial Catalog=SQLAuthority; Integrated Security=SSPI")

# Define variables and the values to be inserted
$EmployeeID = 1
$FirstName = 'Pinal'
$LastName = 'Dave'

# Open database connection
$con.Open()

# Create SQL Insert Statement for table EmployeeMaster with the values
$stmt_insert = "INSERT INTO [EmployeeMaster] ([EmployeeID],[FirstName],[LastName]) VALUES($EmployeeID ,'$FirstName' ,'$LastName')"

# Create command to execute to SQL connection
$cmd = $con.CreateCommand()
$cmd.CommandText = $stmt_insert

# Invoke the Insert statement
$cmd.ExecuteNonQuery()

Once we execute the above script, the data will be inserted into the EmployeeMaster table.

To fetch the data from the table, we can use the Invoke-Sqlcmd cmdlet:

Add-PSSnapin SqlServerCmdletSnapin100
$DataSet = Invoke-Sqlcmd -Database "SQLAuthority" -Query "SELECT * FROM [EmployeeMaster]" -ServerInstance .

foreach ($element in $DataSet)
{
    $element
}

# Close database Connection
$con.Close()

We can run all the statements together in one PowerShell script and use Windows PowerShell ISE to execute it.

Using PowerShell to connect to SQL Server and run queries can be a useful tool for automating tasks and performing database operations. It provides flexibility and ease of use, allowing users to interact with SQL Server in a more efficient way.

Have you ever had a similar requirement? Share your experience and thoughts in the comments below!