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

  • Comments posted to this topic are about the item Insert data into a SQL Server Table using Powershell using Invoke-SQLc

  • 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

  • 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]

  • 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

  • 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]

  • 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

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

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