Introduction

In many data integration scenarios, you’ll need to import data from a CSV file into a SQL Server database. PowerShell is a powerful tool that allows you to automate this process efficiently. In this post, we’ll walk you through the steps to insert data from a CSV file into a SQL Server table using PowerShell with Windows authentication.

Prerequisites

Before we begin, ensure that you have the following:

• A CSV file containing the data you want to import.
• A SQL Server instance installed locally with a corresponding database and table.
• PowerShell installed on your machine.

Step 1: Prepare Your CSV File

Ensure your CSV file is structured correctly and contains the data you want to insert into the SQL Server table. For example, let’s assume your CSV file (data.csv) looks like this:

Column1,Column2,Column3
Value1,Value2,Value3
Value4,Value5,Value6

Step 2: Create a Table in SQL Server

Before importing the data, make sure the table in your SQL Server database matches the structure of your CSV file. For example:

CREATE TABLE YourTableName (
    Column1 NVARCHAR(50),
    Column2 NVARCHAR(50),
    Column3 NVARCHAR(50)
);

Step 3: Write the PowerShell Script

The following PowerShell script reads the CSV file and inserts its data into the SQL Server table using Windows authentication.

# Define SQL Server connection parameters
$server = "your_server_name"
$database = "your_database_name"
$table = "your_table_name"

# Path to your CSV file
$csvPath = "C:\path\to\your\file.csv"

# Create a connection to SQL Server with integrated security
$connectionString = "Server=$server;Database=$database;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

# Open the connection
$connection.Open()

# Read CSV file
$data = Import-Csv -Path $csvPath

# Iterate through the data and perform insertions
foreach ($row in $data) {
    $query = "INSERT INTO $table (Column1, Column2, Column3) VALUES ('$($row.Column1)', '$($row.Column2)', '$($row.Column3)')"
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $command.ExecuteNonQuery()
}

# Close the connection
$connection. Close()

Step 4: Run the Script

1. Save the above script with a .ps1 extension (e.g., insert_data.ps1).
2. Open PowerShell and navigate to the directory where your script is located.
3. Run the script by typing .\insert_data.ps1.

Conclusion

This PowerShell script provides a simple and effective way to insert data from a CSV file into a SQL Server database using Windows authentication. It can be particularly useful for automating data import tasks and integrating various data sources into your SQL Server environment.