Inserting(Bulk insert) Data from CSV to MS SQL Server Using PowerShell: A Step-by-Step Guide

PowerShell is a powerful scripting language that can automate many tasks, including database operations. One common task is importing data from a CSV file into a SQL Server database. This guide will show you how to use PowerShell to perform this task efficiently using the SqlBulkCopy class, which is optimized for bulk insert operations.

Embark on a journey of continuous learning and exploration with DotNet-FullStack-Dev. Uncover more by visiting our https://dotnet-fullstack-dev.blogspot.com reach out for further information.

Prerequisites

Before you begin, ensure you have the following:

1. A CSV file with your data.
2. A SQL Server database and table where you want to insert the data.
3. PowerShell installed on your machine.

Real-Time Example

Suppose you have a CSV file named orders.csv containing order data that you want to insert into a SQL Server table named Orders.

orders.csv

OrderID,CustomerName,OrderDate,TotalAmount
1,John Doe,2023-01-01,100.00
2,Jane Smith,2023-01-02,150.00
3,Bob Johnson,2023-01-03,200.00

SQL Server Table Structure

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    OrderDate DATE,
    TotalAmount DECIMAL(18, 2)
);

Step-by-Step Guide

Step 1: Prepare Your Environment

Ensure you have a SQL Server database and table ready to receive the data. The table structure should match the columns in your CSV file.

Step 2: PowerShell Script

Here’s a PowerShell script to insert data from orders.csv into the Orders table in your SQL Server database:

# Define the path to your CSV file
$csvFilePath = "C:\path\to\your\orders.csv"

# Define the SQL Server connection string
$connectionString = "Server=your_server;Database=your_database;Integrated Security=True;"

# Define the destination table name
$destinationTableName = "Orders"

# Load the CSV data into a DataTable
$dataTable = New-Object System.Data.DataTable

# Read CSV file and add columns to the DataTable
$csvData = Import-Csv -Path $csvFilePath
$csvData[0].PSObject.Properties | ForEach-Object {
    $dataTable.Columns.Add($_.Name)
}

# Add rows to the DataTable
foreach ($row in $csvData) {
    $dataRow = $dataTable.NewRow()
    $row.PSObject.Properties | ForEach-Object {
        $dataRow[$_.Name] = $_.Value
    }
    $dataTable.Rows.Add($dataRow)
}

# Create a SQL Bulk Copy object
$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connectionString)
$bulkCopy.DestinationTableName = $destinationTableName

# Perform the bulk copy
try {
    $bulkCopy.WriteToServer($dataTable)
    Write-Host "Data successfully inserted into the table."
} catch {
    Write-Host "An error occurred: $_"
} finally {
    $bulkCopy.Close()
}

Explanation

Define the CSV file path and SQL Server connection string:

• Update $csvFilePath with the path to your orders.csv file.
• Update $connectionString with your SQL Server connection details.

Load the CSV data into a DataTable:

• Use Import-Csv to read the CSV file.
• Create a DataTable and add columns based on the CSV headers.
• Add rows to the DataTable from the CSV data.

Create and configure the SqlBulkCopy object:

• Initialize SqlBulkCopy with the SQL Server connection string.
• Set the DestinationTableName property to Orders.

Perform the bulk copy:

• Use WriteToServer to insert the data into the SQL Server table.
• Handle any exceptions that may occur during the process.

Running the Script

1. Save the script to a .ps1 file (e.g., InsertDataFromCSV.ps1).
2. Open PowerShell as an administrator.
3. Navigate to the directory where the script is saved.
4. Run the script using the following command:

.\InsertDataFromCSV.ps1

Conclusion

Using PowerShell to insert data from a CSV file into a SQL Server table is a powerful and efficient method for handling bulk data operations. The SqlBulkCopy class is optimized for this purpose, ensuring that the data insertion process is quick and reliable. By following the steps outlined in this guide, you can easily automate this task and integrate it into your data processing workflows.