csv - Issue with loading multiple SQL query results sets in datatables with Powershell

Question

I am doing 2 separate SQL queries on separate databases / connections in a Powershell script. The goal is to export the results of both requests into a single CSV file.

What I am doing now is:

# Create a data table for Clients
$ClientsTable = new-object "System.Data.DataTable"

# Create text commands
$ClientsCommand1 = $connection1.CreateCommand()
$ClientsCommand1.CommandText = $ClientsQuery1

$ClientsCommand2 = $connection2.CreateCommand()
$ClientsCommand2.CommandText = $ClientsQuery2

# Get Clients results
$ClientsResults1 = $ClientsCommand1.ExecuteReader()
$ClientsResults2 = $ClientsCommand2.ExecuteReader()

# Load Clients in data table
$ClientsTable.Load($ClientsResults1)
$ClientsTable.Load($ClientsResults2)

# Export Clients data table to CSV
$ClientsTable | export-csv -Encoding UTF8 -NoTypeInformation -delimiter ";" "C:estclients.csv"

where $connection1 and $connection2 are opened System.Data.SqlClient.SqlConnection.

Both requests work fine and both output data with exactly the same columns names. If I export the 2 results sets to 2 separate CSV files, all is fine.

But loading the results in the data table as above fails with the following message:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

If instead I switch the order in which I load data into the data tables, like

$ClientsTable.Load($ClientsResults2)
$ClientsTable.Load($ClientsResults1)

(load second results set before the first one), then the error goes away and my CSV is generated without any problem with the data from the 2 requests. I cannot think of why appending data in one way, or the other, would trigger this error, or work fine.

Any idea?

question from:https://stackoverflow.com/questions/65599255/issue-with-loading-multiple-sql-query-results-sets-in-datatables-with-powershell

Answer 1

I'm skeptical reversing the order works. More likely, it's doing something like appending to the csv file that was already created from the first attempt.

It is possible, though, that different primary key definitions from the original data could produce the results you're seeing. Datatable.Load() can do unexpected things when pulling data from an additional sort. It will try to MERGE the data rather than simply append it, using different matching strategies depending on the overload and argument. If the primary key used for the one of the tables causes nothing match and no records to merge, but the primary key for the table matched everything, that might explain it.

If you want to just append the results, what you want to do instead is Load() the first result into the datatable, export to CSV, clear the table, load the second result into the table, and then export again in append mode.