December 14, 2015 at 2:32 am
Hi Everyone
I am writing a very basic scripts which pulls info from one DB and pushes it to another. I wont go into reasons for doing this etc.
The pulling is working correctly and I am building an array of the results.
When I do the following is when i run into issues:
$data | ForEach-Object {Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "insert into $table (SessionIdTime, SessionIdSeq) VALUES ('$_.SessionIdTime', '$_.SessionIdSeq')"}
The results in the table come out as follows:
SessionIdTimeSessionIdSeq
System.Data.DataRow.SessionIdTimeSystem.Data.DataRow.SessionIdSeq
So it is recognising the $_ part but then it looks to me like the period (.) is causing the next part to be text. I have tried various ways of escaping it with no luck.
I can get it to work by creating a variable for each one in the foreach loop but this seems inefficient i.e:
$data | ForEach-Object {$SessionIDTime = $_.SessionIDTime ; $SessionIdSeq = $_.SessionIdSeq ; Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "insert into $table (SessionIdTime, SessionIdSeq) VALUES ('$SessionIdTime', '$SessionIdSeq')"}
Your assistance with this would be highly appreciated.
Regards
Brendon
December 14, 2015 at 2:57 am
It has nothing to do with escaping the period, because you have the object in single quotes PowerShell evaluates the object and is returning the type. You have to force PowerShell to evaluate the expression in order to return the property value by wrapping it in "$()". Also it can make it easier to read if you do this:
foreach ($d in $data) {
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "insert into $table (SessionIdTime, SessionIdSeq) VALUES ('$($d.SessionIdTime)', '$($d.SessionIdSeq)')"
}
However you can also make your query more readable by creating that in a here-string and then passing it to the Invoke-Sqlcmd:
foreach ($d in $data) {
$qry = @"
INSERT INTO $table (SessionIdTime, SessionIdSeq)
VALUES ('$($d.SessionIdTime)', '$($d.SessionIdSeq)')
"@
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query $qry
}
The other part to consider, since you did not provide how you are actually creating that dataset is you may need to reference the table in your data table. You can see how to work with datasets in PowerShell here[/url].
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
December 14, 2015 at 3:31 am
Hi Shawn
Thanks so much for your assistance.
Works perfectly
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply