none
Passing a powershell variable to a invoke-sqlcmd

    Question

  • Hi!

    Im trying to pass a variable in powershell into an Invoke-SqlCmd i.e.

    $var1 = 'Test'
    $var2 = 'Test2'
    
    Invoke-sqlcmd -Query "Insert into mytable(col1, col2) values ($var1, $var2)" -ServerInstance "myInstance" -Database "myDB"

    Any idea how to do this?

    Thanks,

    Zoe

    Friday, July 13, 2018 8:28 AM

Answers

  • Lots of quotes but not the right ones.
    $var1 = 'Test'
    $var2 = 'Test2'
    $sql = "Insert into mytable(col1, col2) values ('$var1', '$var2')"
    Write-Host $sql
    Invoke-sqlcmd -Query $sql -ServerInstance myInstance -Database myDB


    \_(ツ)_/

    • Marked as answer by Zoe.Ohara Friday, July 13, 2018 10:54 AM
    Friday, July 13, 2018 9:09 AM
    Moderator

All replies

  • Lots of quotes but not the right ones.
    $var1 = 'Test'
    $var2 = 'Test2'
    $sql = "Insert into mytable(col1, col2) values ('$var1', '$var2')"
    Write-Host $sql
    Invoke-sqlcmd -Query $sql -ServerInstance myInstance -Database myDB


    \_(ツ)_/

    • Marked as answer by Zoe.Ohara Friday, July 13, 2018 10:54 AM
    Friday, July 13, 2018 9:09 AM
    Moderator
  • Brilliant thanks!
    Friday, July 13, 2018 10:55 AM
  • You can also do it this way which is better if you have many variables and want to externalize the SQL in a file (good for large SQL statements'

    # top of script or from a file
    $sql = 'Insert into mytable(col1, col2) values ($(var1), $(var2))'
    $sqlfile = 'c:\temp\myquery.sql'

    # later in script
    $var1 = 'Test' $var2 = 'Test2' $variables = @( "Var1 = '$var1'", "Var2 = '$var2'" ) Invoke-sqlcmd -Query $sql -Variable $variables -ServerInstance myInstance -Database myDB

    #or with a file
    Invoke-Sqlcmd -InputFile $sqlfile -Variable $variables -ServerInstance myInstance -Database myDB



    \_(ツ)_/





    Friday, July 13, 2018 11:16 AM
    Moderator