<< SQL Server Hosting - HostForLIFE :: Most Important SQL Commands | Europe SQL Hosting - HostForLIFEASP.NET :: Triggers in SQL Server >>

SQL Server Hosting - HostForLIFE :: Insert Data Into SQL Server Table Using PowerShell

July 7, 2021 06:30 by author

Peter

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!
We will define the variables. We have 5 variables ‘serverName’, ‘databaseName’, ‘tableName’, ‘studentName’ and ‘standard’.

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

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

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()
}

Close the connection of SQL.
$Connection.Close();

Here is the whole code for inserting the data into the table.
$serverName = "HostForLIFE"
$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