Insert data into a SQL Server Table using Powershell using Invoke-SQLc

Question

Insert data into a SQL Server Table using Powershell using Invoke-SQLc

VishnuGupthanSQLPowershellDBA

Ten Centuries

Points: 1230
More actions
January 15, 2018 at 12:37 pm

#409959

Comments posted to this topic are about the item Insert data into a SQL Server Table using Powershell using Invoke-SQLc
Vishnu Gupthan
PowershellAcademy
www.powershellacademy.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ali M SSC Veteran Points: 251 More actions · Answer 1

Much simpler and more efficient version (only one round-trip to the SQL server).
If you wanted to update the table with the current state, the valueList could be used to form a MERGE statement.

# Get all services to a list of VALUEs for insert # -join ensures each row is separated with "comma, carriage return" except for the last one $valueList = (Get-Service | ForEach-Object { "( '$($_.Status)', '$($_.Name)', '$($_.DisplayName)')" }) -join ",`n" $insertquery=@" INSERT INTO [dbo].[ServiceTable] ([Status] ,[Name] ,[DisplayName]) VALUES $valueList "@ Invoke-SQLcmd -ServerInstance 'KILIKOOD-PC\MSSQLSERVER,1433' -query $insertQuery -U sa -P test123 -Database Fantasy

MadAdmin SSChampion Points: 11261 More actions · Answer 2

using DBATools

$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers

Much better than foreach

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Sue_H SSC Guru Points: 90860 More actions · Answer 3

MadAdmin - Thursday, February 22, 2018 6:54 AM
using DBATools
$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers
Much better than foreach

Importing data from an existing file is not at all what they are doing though.
They are getting all services and then for each service, get the service name, status and display name.
But as the author explained, it's an example for using Invoke-SQLcmd.

Sue

MadAdmin SSChampion Points: 11261 More actions · Answer 4

Sue_H - Thursday, February 22, 2018 7:32 AM
MadAdmin - Thursday, February 22, 2018 6:54 AM
using DBATools
$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers
Much better than foreach
Importing data from an existing file is not at all what they are doing though.
They are getting all services and then for each service, get the service name, status and display name.
But as the author explained, it's an example for using Invoke-SQLcmd.
Sue

Was showing the method, not the solution. Can be applied using any array or dataset.
So,

$var = Get-Service 

Then use that as a parameter in the writedbatable commandlet.
Simplifies the process into 2 lines.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Sue_H SSC Guru Points: 90860 More actions · Answer 5

MadAdmin - Thursday, February 22, 2018 8:43 AM
Sue_H - Thursday, February 22, 2018 7:32 AM
MadAdmin - Thursday, February 22, 2018 6:54 AM
using DBATools
$datatable = Import-Csv C:\temp\customers.csv | Out-DbaDataTable
Write-DbaDataTable -SqlServer sql2014 -InputObject $datatable -Table mydb.dbo.customers
Much better than foreach
Importing data from an existing file is not at all what they are doing though.
They are getting all services and then for each service, get the service name, status and display name.
But as the author explained, it's an example for using Invoke-SQLcmd.
Sue
Was showing the method, not the solution. Can be applied using any array or dataset.
So,
$var = Get-Service
Then use that as a parameter in the writedbatable commandlet.
Simplifies the process into 2 lines.

Yup..I get that and I use DBATools. Calling the function Write-DbaDataTable from DBATools which does the ForEach. So it's more a matter of using functions in Powershell. The ForEach isn't elimated, it's just used in the function.

Sue