Saving results output to SQL database table

Question

Successfully generated the results from PowerShell, but don't know how to save this to a SQL database table ... as we need info in table for further manipulation.

Get-Content "D:\Files\Legal.doc"
| Select-String -Pattern  "Currency term loan in an aggregate amount equal to the"
| Select-Object Path, LineNumber, Line
| Export-Csv -Path D:\Results.csv -NoTypeInformation -Encoding ASCII

Need help for saving about results in SQL table.

FTR, the code you posted can't possibly work. Am I correct to assume that you actually have all of that in one line, and just wrapped it for this question? — Ansgar Wiechers, Jul 14 '18 at 12:07

score 1 · Answer 1 · answered Jul 14 '18 at 13:06

While @postanote's answer will work, be aware that it's susceptible to SQL injection errors or compromise because it's concatenating strings, and any of your values which contain a ' will break it.

I recommend using Out-DbaDataTable and Write-DbaDataTable from dbatools.

$database = 'DBName'
$server = '.'
$table = 'DBTableName'

Import-CSV .\yourcsv.csv | out-dbadatatable | write-dbadatatable -sqlinstance $server -table $table -database $database;

This should also run much faster than individual INSERT statements.

score -1 · Accepted Answer · edited Jun 20 '20 at 09:12

Try this approach...

$database = 'DBName'
$server = '.'
$table = 'DBTableName'

Import-CSV .\yourcsv.csv | ForEach-Object {Invoke-Sqlcmd `
-Database $database -ServerInstance $server `
-Query "insert into $table VALUES ('$($_.Column1)','$($_.Column2)')"
}

... as discussed below.

Storing Results from Powershell to SQL Table

How to import data from .csv in SQL Server using PowerShell?

So, this post could be considered a duplicate of the above.

Saving results output to SQL database table

2 Answers2