Insert Data Into SQL Server Table Using PowerShell

In this blog, we will see how to insert data into an SQL table using PowerShell.

Introduction

In this blog, we will create a PowerShell script that inserts the data into a SQL table. In this scenario, we have one SQL table of student details. We insert the Student name, standard, and division into the ‘Student’ table.

Here we have the ‘Student’ table in SQL Server. We have two different columns ‘Name’, ‘STD’. We will enter the 5 static details of students.

Let’s get started! 

1. We will define the variables. We have 5 variables ‘serverName’, ‘databaseName’, ‘tableName’, ‘studentName’ and ‘standard’. 

    $serverName = "LAPTOP4F1K83KS"  
    $databaseName = "StudentDetails"  
    $tableName = "dbo.Student"
    $studentName = 'John','Debo','Carry','Mini'  
    $standard = '5'

2. We will establish a new connection for the SQL Server database using the below code. We will use the ‘ServerName’ and ‘databaseName’ for establishing the connection string.

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

3. We will apply for each student’s name and execute the command for inset into the SQL table. Here we use into query command and execute the command. This query will insert the employee name and standard field in the student table. 

    foreach($Name in $studentName){  
      $insertquery="   
      INSERT INTO $tableName  
          ([Name],[STD])  
        VALUES   
          ('$Name','$standard')"   
      $Command.CommandText = $insertquery  
      $Command.ExecuteNonQuery()  
    }

4. Close the connection of SQL.

   $Connection.Close();

Here is the whole code for inserting the data into the table. 

 $serverName = "LAPTOP4F1K83KS"  
 $databaseName = "StudentDetails"  
 $tableName = "dbo.Student"
 $studentName = 'John','Debo','Carry','Mini'  
 $standard = '5'   
 $Connection = New-Object System.Data.SQLClient.SQLConnection  
 $Connection.ConnectionString = "server='$serverName';database='$databaseName';trusted_connection=true;"  
 $Connection.Open()  
 $Command = New-Object System.Data.SQLClient.SQLCommand  
 $Command.Connection = $Connection  
 foreach($Name in $studentName){  
   $insertquery="   
   INSERT INTO $tableName  
       ([Name],[STD])  
     VALUES   
       ('$Name','$standard')"   
   $Command.CommandText = $insertquery  
   $Command.ExecuteNonQuery()  
 }  
 $Connection.Close();  

Output

Conclusion

So here we can easily insert the data into the SQL server table, hope this helps.

Happy Scripting!!