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;

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;

• azure
• azure-data-factory

1 Answer 1

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: