error supplying multiple values using tsql merge statement with invoke-sqlcmd

This topic contains 3 replies, has 3 voices, and was last updated by  Shafiq 2 years, 7 months ago.

  • Author
    Posts
  • #25323

    Shafiq
    Participant

    Can someone inspect my code and tell me why i am getting the error below when i try to insert more than one value into a table record.

    #I created an 'inventory' database in sql server management studio first.
    #Create tables in inventory database
    #Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query "CREATE TABLE [dbo].[Systems1]([Name] [nvarchar](75) NOT NULL) ON [PRIMARY]"
    #Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query "CREATE TABLE [dbo].[Systems2]([Name] [nvarchar](75) NOT NULL,[SerialNumber] [nvarchar](75) NOT NULL) ON [PRIMARY]"

    #getting some hardware information from wmi
    $compsys = gwmi win32_ComputerSystem
    $bios = gwmi win32_ComputerSystem

    #Executes successfully when single value is inserted
    $Query1 = "WITH CTE_T (Name) AS (SELECT '$($Compsys.name)')
    MERGE INTO dbo.Systems1 AS T
    USING (SELECT * FROM CTE_T) AS S
    ON (T.Name = S.Name)
    WHEN MATCHED THEN
    UPDATE SET T.Name = S.Name
    WHEN NOT MATCHED THEN
    INSERT VALUES(S.Name);"

    #throws error when multiple values inserted
    $Query2 = "WITH CTE_T (Name,SerialNumber) AS (SELECT '$($Compsys.name)', '$($bios.serialnumber)')
    MERGE INTO dbo.Systems2 AS T
    USING (SELECT * FROM CTE_T) AS S
    ON (T.Name = S.Name)
    WHEN MATCHED THEN
    UPDATE SET T.SerialNumber = S.SerialNumber
    UPDATE SET T.Name = S.Name
    WHEN NOT MATCHED THEN
    INSERT VALUES(S.Name,S.SerialNumber);"

    Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query $Query2

    Query2 throws:
    Invoke-Sqlcmd : A MERGE statement must be terminated by a semi-colon (;).
    At line:29 char:1
    + Invoke-Sqlcmd -ServerInstance . -Database Inventory -Query $Query2
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

  • #25338

    Don Jones
    Keymaster

    You're really asking a T-SQL question, not a PowerShell question. I'd suggest asking at StackOverflow.com, or someplace similar – you're much more likely to get a good answer. You'd get this same error if you tried to run the query in a query window of SQL Server Management Studio.

  • #25344

    Rob Simmers
    Participant

    Couple of things.

    1 – You are creating the BIOS object with the same WMI class Win32_ComputerSystem, it should be Win32_BIOS. If the serial column does not accept nulls, it could be why you are getting an error
    2 – Highly recommend the use of here-strings, see the examples below for the queries

    $compsys = Get-WMIObject -Class Win32_ComputerSystem -Property Name | Select Name
    $bios = Get-WMIObject -Class Win32_BIOS -Property SerialNumber | Select SerialNumber
    
    #Executes successfully when single value is inserted
    $Query1 = @"
    WITH CTE_T (Name) AS (SELECT '$($compsys.name)')
        MERGE INTO dbo.Systems1 AS T
        USING (SELECT * FROM CTE_T) AS S
        ON (T.Name = S.Name)
        WHEN MATCHED THEN
        UPDATE SET T.Name = S.Name
    WHEN NOT MATCHED THEN
    INSERT VALUES(S.Name);
    "@
    
    #throws error when multiple values inserted
    $Query2 = @"
    WITH CTE_T (Name,SerialNumber) AS (SELECT '$($compsys.name)', '$($bios.serialnumber)')
        MERGE INTO dbo.Systems2 AS T
        USING (SELECT * FROM CTE_T) AS S
        ON (T.Name = S.Name)
        WHEN MATCHED THEN
        UPDATE SET T.SerialNumber = S.SerialNumber
        UPDATE SET T.Name = S.Name
    WHEN NOT MATCHED THEN
    INSERT VALUES(S.Name,S.SerialNumber);
    "@
    
    $Query1
    $Query2
    
  • #25355

    Shafiq
    Participant

    Thanks for the tips Don and Rob. The duplicate class was a typo. The actual problem was with my sql syntax. After executing the statement in sql server management studio I was able to see exactly where the fail was happening. I only needed to specify the UPDATE SET clause once and then separate each column to be updated with commas. Instead I was creating UPDATE SET statements for each column I wanted to update.

    Correct Syntax:

    WITH CTE_T (Name,SerialNumber) AS (SELECT '$($compsys.name)', '$($bios.serialnumber)')
    MERGE INTO dbo.Systems2 AS T
    USING (SELECT * FROM CTE_T) AS S
    ON (T.Name = S.Name) AND (T.SerialNumber = S.SerialNumber)
    WHEN MATCHED THEN
    UPDATE SET T.Name = S.Name, T.SerialNumber = S.SerialNumber
    WHEN NOT MATCHED THEN
    INSERT VALUES(S.Name,S.SerialNumber);

You must be logged in to reply to this topic.