Modify Azure Data Factory Expression to include an Alias

I once received assistance converting the following T-SQL code to Azure Data Factory expression.

The T-SQL is:

SELECT
  deltaTable.*
FROM dbo.deltaTable
LEFT OUTER JOIN dbo.targetTable
  ON deltaTable.signature = targetTable.signature
WHERE targetTable.signature IS NULL

The pipeline expression is:

@concat('SELECT ',pipeline().parameters.DeltaTable,'.* FROM ',pipeline().parameters.tempdb,'.',pipeline().parameters.Domain,'.',pipeline().parameters.DeltaTable,' LEFT OUTER JOIN ',pipeline().parameters.tempdb,'.',pipeline().parameters.Domain,'.',pipeline().parameters.TableName,' ON ',pipeline().parameters.DeltaTable,'.signature = ',pipeline().parameters.TableName,'.signature WHERE ',pipeline().parameters.TableName,'.signature IS NULL')

Can someone help further the modify the Azure Data Factory expression for the following T-SQL code:

SELECT 
    DeltaTable.*
FROM dlt.DeltaTable
LEFT OUTER JOIN dbo.TableName AS tgt
    ON dlt.DeltaTable.signature = tgt.signature
WHERE tgt.signature IS NULL;

Solution

Can someone help further the modify the Azure Data Factory expression for the following T-SQL code: SELECT DeltaTable. * FROM dlt.DeltaTable LEFT OUTER JOIN dbo.TableName AS tgt ON dlt.DeltaTable.signature = tgt.signature
WHERE tgt.signature IS NULL;

You will probably get the error when you directly add the expression. as below

To resolve this error, follow the below steps:

First you need to edit your source dataset by checking or unchecking the first row as header option.

You can use below dynamic expression in your query:


@concat('SELECT ',pipeline().parameters.DeltaTable,'.\* FROM ',pipeline().parameters.DeltaTable,' LEFT OUTER JOIN ',pipeline().parameters.TargetTable,' ON ',pipeline().parameters.DeltaTable,'.signature = ',pipeline().parameters.TargetTable,'.signature WHERE ',pipeline().parameters.TargetTable,'.signature IS NULL')

Now, go to the mapping section, import the schemas, and make the necessary changes to ensure that the source and destination columns match your requirements. Check and preview your data in source tab.

Now run the pipeline. It will run successfully.

Output: