Querying data from SQL Server table with Powershell

Question

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?

What is $result? And it seems like you never execute $command — marsze
– marsze, Commented Apr 26, 2021 at 15:19
If you're using windows authentication, there's no need for hardcoded credentials, right? — OwlsSleeping
– OwlsSleeping, Commented Apr 26, 2021 at 16:04

Cpt.Whale · Accepted Answer · 2021-04-26 16:05:06Z

2

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

answered Apr 26, 2021 at 16:05

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

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

1 Comment

Pepe Alvarez Over a year ago

But, how do you add username and password to the Read-SqlTableData?

Collectives™ on Stack Overflow

Querying data from SQL Server table with Powershell

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related