Inserting records into MS SQL server from .sql files using Invoke-Sqlcmd in PowerShell

Invoke-Sqlcmd is a PowerShell commandlet which is specifically designed to run SQL commands. This is similar to New-Object System.Data.SqlClient.SqlConnection, which is part of the SMO (Server Management objects), but different and without the complications of object creation, etc.

Here's some sample code generated by Gemini to show how it can be used.

# Requires the SqlServer module: Install-Module -Name SqlServer

# --- Configuration Variables ---
$SqlServerInstance = "YOUR_SERVER_NAME\SQLEXPRESS" # e.g., "localhost\SQLEXPRESS"
$DatabaseName = "YourDatabaseName"
$SqlFilesPath = "C:\Path\To\Sql\Files"
# -------------------------------

## 🔍 Find and Execute SQL Files
<#
    This command gets all files with the .sql extension
    in the specified path and pipes each one to the ForEach-Object loop.
#>
Get-ChildItem -Path $SqlFilesPath -Filter "*.sql" | ForEach-Object {
    $FilePath = $_.FullName
    Write-Host "--- Executing SQL script: $FilePath ---"

    try {
        # Execute the SQL script file using Invoke-Sqlcmd
        # The -InputFile parameter takes the path to the file.
        Invoke-Sqlcmd `
            -ServerInstance $SqlServerInstance `
            -Database $DatabaseName `
            -InputFile $FilePath `
            -ConnectionTimeout 60 ` # Timeout for the connection attempt (seconds)
            -QueryTimeout 120    # Timeout for the query execution (seconds)

        Write-Host "✅ Successfully executed $FilePath"
    }
    catch {
        Write-Error "❌ Error executing $FilePath: $($_.Exception.Message)"
        # You might want to stop the script or log the error in a real-world scenario
    }
    Write-Host ""
}

Write-Host "Script execution complete."