Querying data from SQL Server table with Powershell

Due to some technical reasons I need to upload an xlsx file records into a SQL Server table, the main goal is that to have some kind of incremental load: we have a new file per a day and I want to compare the database's table with the new xlsx file, go through the first 50'000 rows (for that I am ordering the first ID rows descendingly) then if we have rows which are not the same then delete in the SQL Server table and insert those rows from the xlsx file. The problem is that I am trying to connect to the database's table, but it looks like that without any result.

$datasource = "server" 
$user = "account"
$pass = "password"
$database = "database"
$connectionString = "Server=$dataSource;uid=$user; pwd=$pwd; Database=$database; Integrated Security=True;"


$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

$query = "SELECT * FROM [XXX].[dbo].[Originaltable]"

$command = $connection.CreateCommand()
$command.CommandText = $query

$table = new-object "System.Data.DataTable"
$table.Load($result)

What am I doing wrong?

Solution

In your example, you are missing one command to actually connect:

$command.CommandText = $query
<# Add: #> $result = $command.ExecuteReader()
$table = New-Object System.Data.DataTable
$table.Load($result)

I recommend you try using the SqlServer powershell module commands. They are essentially the same thing, so it's personal preference.

# Invoke-Sqlcmd can take a query and returns an object
$table = Invoke-Sqlcmd -ServerInstance "$Server\$InstanceName" -Database $database -Username $user -Password $pass `
    -Query 'SELECT * FROM [XXX].[dbo].[Originaltable]'

# Read and Write-SqlTableData are better when you don't need to filter anything out:
# Example using integrated security
$table = Read-SqlTableData -ServerInstance "$Server\$InstanceName" `
    -SchemaName dbo `
    -Database $database `
    -TableName Originaltable `
    -TopN 50000