SharePoint Consulting Blog

Overview:

We implemented a PowerShell script for a Construction Engineering Company having headquarters in Boston, Massachusetts, United States; We came across a scenario was to insert records in MS SQL Server Database from the PowerShell script. In this blog, we will see how to enter data into the SQL Server - Database Table using PowerShell script. Let’s take an example of one real-life business use case.

Here we will have one table named “Employee” in SQL Server Database. We have two different columns named “EmpName” and “Designation”. We want to enter some Employee Information into this table. So, how can we achieve this using PowerShell?

Let’s get started!

Let’s define some variables to insert the values to SQL Server - Database Table. Here we have 5 variables for 'Server', 'Database', 'TableName', ‘EmpName’, and ‘Designation’.

 $EmpName = 'Tejal','Khyati','Anikesh','Harsh'  
 $Designation = 'Developer'  
 $server = "Dev220"  
 $Database = "PoCDatabase"  
 $TableName = "dbo.Employee"

Establish a connection for the SQL Server database using the below code snippet. Here we use the ‘Server’ and ‘Database’ variables to generate connection string.

 $Connection = New-Object System.Data.SQLClient.SQLConnection  
 $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"  
 $Connection.Open()  
 $Command = New-Object System.Data.SQLClient.SQLCommand  
 $Command.Connection = $Connection

We will apply a loop for each employee's name and execute the command for inset into the table.

 foreach($Name in $EmpName){  
   $insertquery="   
   INSERT INTO $TableName  
       ([EmpName],[Designation])  
     VALUES   
       ('$Name','$Designation')"   
   $Command.CommandText = $insertquery  
   $Command.ExecuteNonQuery()  
 }

Here we use the Insert into query command and execute the command. This query will insert the Employee Name and Designation field values in the table.

Close the connection of SQL. Use the following code snippet for the same.
```
 $Connection.Close();  
```
Here is the complete code snippet to insert the data into the table.

 $EmpName = 'Tejal','Khyati','Anikesh','Harsh'  
 $Designation = 'Developer'  
 $server = "Dev220"  
 $Database = "KDC"  
 $TableName = "dbo.Employee"  
 $Connection = New-Object System.Data.SQLClient.SQLConnection  
 $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"  
 $Connection.Open()  
 $Command = New-Object System.Data.SQLClient.SQLCommand  
 $Command.Connection = $Connection  
 foreach($Name in $EmpName){  
   $insertquery="   
   INSERT INTO $TableName  
       ([EmpName],[Designation])  
     VALUES   
       ('$Name','$Designation')"   
   $Command.CommandText = $insertquery  
   $Command.ExecuteNonQuery()  
 }  
 $Connection.Close();

Let’s execute our PowerShell script. This will insert the following code to SQL Server Database Table.

Conclusion:

This is how we can insert the data to SQL Server using PowerShell Script, hope this helps. Happy Scripting!!!