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:\test\clients.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?

Joel Coehoorn · Accepted Answer · 2021-01-06 17:03:16Z

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.

edited Jan 6, 2021 at 17:03

answered Jan 6, 2021 at 16:52

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrUpsidown Over a year ago

Reversing the order does work in my case. export-csv to my knowledge just exports the data as CSV. It doesn't append to an existing file unless the -Append flag is used. I failed to mention that the data in the 2 results sets is 100% different (no common records in the 2 sets) so I thought it would indeed append records from the second result set to the first one and not complain about anything (btw I still don't know why I get this error). That said, exporting in append mode as you suggested should work in my case. I'll try that tomorrow. Thanks.

MrUpsidown Over a year ago

No issues when using export-csv with -Append flag. Thanks for your help.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related