My copy activity is attempting query on two tables in a database called OSCAR. One of the tables 'toMasterPolicy' in the database uses the schema dlt, whereas the table with the same 'toMasterPolicy' uses the schema dbo.

When I execute the following query below, I get the error:

Clearly the problem is that I'm using the same name for each table. In a previous question I asked if I could add suffix to a 'TableName' to change the name of the table. It was suggested I could do the following:

@concat(pipeline().parameters.TableName,'V2')

Can someone take a look at my expression and either help me modify the expression to add a suffix as suggested i.e. @concat(pipeline().parameters.TableName,'V2')

Or show me how to use as correlation name to distinguish the tables 'toMasterPolicy'

My expression is as follows:

@

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

My copy activity is attempting query on two tables in a database called OSCAR. One of the tables 'toMasterPolicy' in the database uses the schema dlt, whereas the table with the same 'toMasterPolicy' uses the schema dbo.

When I execute the following query below, I get the error:

Clearly the problem is that I'm using the same name for each table. In a previous question I asked if I could add suffix to a 'TableName' to change the name of the table. It was suggested I could do the following:

@concat(pipeline().parameters.TableName,'V2')

Can someone take a look at my expression and either help me modify the expression to add a suffix as suggested i.e. @concat(pipeline().parameters.TableName,'V2')

Or show me how to use as correlation name to distinguish the tables 'toMasterPolicy'

My expression is as follows:

@

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

• azure-data-factory

• Waht is source and sink – Pratik Lad Commented Jan 21 at 10:54
• Hi @PratikLad, thanks for getting in touch. The source is dbo.toMasterPolicy and the sink is ADLS. However, please bear in the mind the expression is executed on the source database and the results of the expression is simply sent to ADLS. The main issue here is that expression used someway of handling tables with the same name. I hope I'm making sense – Patterson Commented Jan 21 at 11:17
• So, I guess what I'm saying the sink isn't the issue. Its the expression that I'm having a problem with – Patterson Commented Jan 21 at 11:18

1 Answer 1

The objects "OSCAR.dbo.toMasterPolicy" and "OSCAR.dlt.toMasterPolicy" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

The above error you are getting is the table name you are using the above query has same table to resolve this issue you need to use aliases for each reference to toMasterPolicy Your query should look as below:

SELECT sampletable.* FROM sampletempdb.dlt.sampletable as dlttb LEFT OUTER JOIN sampletempdb.dbo.sampletable as dbotb ON dlttb.signature = dbotb.signature WHERE dlttb.signature IS NULL"

As per above your dynamic expression will look like below:

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