-AsDataTable returns 1 object?

[fenrisvlfr]

Should using the -AsDataTable switch on Invoke-SqlQuery return results as a single DataTable object?

I'm working on a validation project, repointing from one table to another and want to run the same query on both tables to ensure they both return exactly the same result. But when I run

$results = @()
foreach($tableName in $tables){
$query = $myQuery
$results += Invoke-SqlQuery -Query $query -ConnectionName MyOracleConnection -AsDataTable
}

I just get the result set of each appended to $results and can't do something like Compare-Object $results[0] $results[1]

Am I using -AsDataTable correctly, or should I be thinking about this differently?

[mithrandyr]

@fenrisvlfr -- the -AsDataTable simply means that the results will be a one or more datatable objects (by default, Invoke-SqlQuery returns a list of DataRows if there is only one resultset and a list of DataTables if there are more than one resultset).

Compare-Object (in this case), probably won't do what you want, which is to compare the datarows and see if they are the same.

There are a couple of ways that you can handle this. One: use Compare-Object with the -Property value and specify which properties you want to compare, however you will have to do this in a loop (since you would be comparing DataRow to DataRow). If this option is giving you trouble, you can use the -AsStream switch on Invoke-SqlQuery and it will output PSObjects where each column will be a property on the PSObject.

However, this may not be the best way to do what you are trying to do.

I'm going to assume that your two tables are not in the same database (because if they are, just do the analysis in SQL itself, much faster and cleaner). So, say you have your table "someData" that exists in two different databases (and in such a way that you can't do a cross-database query). You have a couple of options. Either bulk-copy the data from one database into a temp table in the second database and do a compare with SQL... or you can do a bulk-copy into an InMemory SQLite database and then do your analysis there.

#prepare SQLite for analysis...
Open-SQLiteConnection -cn dst #this will be inmemory by default.
Invoke-SqlUpdate -Query "CREATE TABLE someTableA ()" #obviously, create the proper columns...
Invoke-SqlUpdate -Query "CREATE TABLE someTableB ()" #obviously, create the proper columns...

#first set of data
Open-SqlConnection -cn src #rest of details.... for first database
Invoke-SqlBulkCopy -SourceConnectionName src -DestinationConnectionName dst -SourceQuery "some query..." -DestinationTable "someTableA" #this assumes the query columns will be the same names as the column names on 'someTableA'
Close-SqlConnection -cn src

Open-SqlConnection -cn src #rest of details.... for second database
Invoke-SqlBulkCopy -SourceConnectionName src -DestinationConnectionName dst -SourceQuery "some query..." -DestinationTable "someTableB" #this assumes the query columns will be the same names as the column names on 'someTableA'
Close-SqlConnection -cn src

#OK, now you have your data in the inMemory SQLite database.. do you analysis
#Missing rows in someTableB
Invoke-SqlQuery -cn dst -Query "SELECT * FROM someTableA EXCEPT SELECT * FROM someTableB"

Basically, SQL itself is really good for doing this kind of analysis, so do it in SQL, not in PowerShell (especially if you are working with a large set of data).
However, if you are working with a small set of data, then Compare-Object can work.

[fenrisvlfr]

Thanks. That is a much better way to accomplish this.