Error within Powershell script

Question

$SQLMerge = "MERGE $SQLDatabase.dbo.$SQLTable Target
USING $SQLTempDatabase.dbo.$SQLTempTable Source
ON (Target.key_id = Source.key_id)
WHEN MATCHED
THEN UPDATE
SET Target.value_parent_account_id = Source.value_parent_account_id,
Target.value_deleted_at = Source.value_deleted_at,
Target.value_name = Source.value_name,
Target.value_current_sis_batch_id = Source.value_current_sis_batch_id,
Target.value_storage_quota = Source.value_storage_quota,
Target.value_default_storage_quota = Source.value_default_storage_quota,
Target.value_default_locale = Source.value_default_locale,
Target.value_default_user_storage_quota = Source.value_default_user_storage_quota,
Target.value_default_group_storage_quota = Source.value_default_group_storage_quota,
Target.value_integration_id = Source.value_integration_id,
Target.value_lti_context_id = Source.value_lti_context_id,
Target.value_consortium_parent_account_id = Source.value_consortium_parent_account_id,
Target.value_course_template_id = Source.value_course_template_id,
Target.value_created_at = Source.value_created_at,
Target.value_updated_at = Source.value_updated_at,
Target.value_workflow_state = Source.value_workflow_state,
Target.value_default_time_zone = Source.value_default_time_zone,
Target.meta_ts = Source.meta_ts
WHEN NOT MATCHED BY TARGET
THEN INSERT (key_id, value_deleted_at, value_name, value_parent_account_id, value_current_sis_batch_id, value_storage_quota, value_default_storage_quota, value_default_locale, value_default_user_storage_quota, value_default_group_storage_quota, value_integration_id, value_lti_context_id, value_consortium_parent_account_id, value_course_template_id, value_created_at, value_updated_at, value_workflow_state, value_default_time_zone, value_uuid, meta_ts)
VALUES (Source.key_id, Source.value_deleted_at, Source.value_name, Source.value_parent_account_id, Source.value_current_sis_batch_id, Source.value_storage_quota, Source.value_default_storage_quota, Source.value_default_locale, Source.value_default_user_storage_quota, Source.value_default_group_storage_quota, Source.value_integration_id, Source.value_lti_context_id, Source.value_consortium_parent_account_id, Source.value_course_template_id, Source.value_created_at, Source.value_updated_at, Source.value_workflow_state, Source.value_default_time_zone, Source.value_uuid, Source.meta_ts);"

Running the INSERT Query

Invoke-SQLCmd -Query $SQLMerge -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword

when I run this code I get the below error. The error relates to the invoke-sqlcmd line

Invoke-SQLCmd : Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Procedure , Line 1.
At K:\Canvas Data 2\upsertaccounts.ps1:169 char:1

• Invoke-SQLCmd -Query $SQLMerge -ServerInstance $SQLInstance -Username ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
• FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Answer 1

Hello

Thank you for your question and reaching out. I can understand you are having query\issues related to SQL query using Powershell.

1. Please remove single quotes from SQL file.
2. Please also validate your SQL file using SQL management studio to verify no syntax errors.

--If the reply is helpful, please Upvote and Accept as answer--