Uncategorized

Export Large Query Result to File (PowerShell)

14. 10. 2024

Export query output to file using PowerShell. Suitable for scenarios when large result set or single column value cannot be exported using SSMS.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
$query = "SELECT 1 AS COL1"
$connectionString = "Server=***;Database=***;Integrated Security=false;User=***;Password=***"
$outputFile = "/***/export.json"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.CommandTimeout = 500
$connection.Open()
$reader = $command.ExecuteReader()
$results = @()
while ($reader.Read()) {
$results += $reader["COL1"]
}
$connection.Close()
$results | Out-File -FilePath $outputFile -Encoding UTF8
$query = "SELECT 1 AS COL1" $connectionString = "Server=***;Database=***;Integrated Security=false;User=***;Password=***" $outputFile = "/***/export.json" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = $connectionString $command = $connection.CreateCommand() $command.CommandText = $query $command.CommandTimeout = 500 $connection.Open() $reader = $command.ExecuteReader() $results = @() while ($reader.Read()) { $results += $reader["COL1"] } $connection.Close() $results | Out-File -FilePath $outputFile -Encoding UTF8
$query = "SELECT 1 AS COL1"

$connectionString = "Server=***;Database=***;Integrated Security=false;User=***;Password=***"

$outputFile = "/***/export.json"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.CommandTimeout = 500

$connection.Open()
$reader = $command.ExecuteReader()

$results = @()
while ($reader.Read()) {
    $results += $reader["COL1"]
}

$connection.Close()

$results | Out-File -FilePath $outputFile -Encoding UTF8

 

3
Share: