Azure Data Factory Expression If else auto create and truncate table

Question

im just quite new in ADF so i would like to know how i can change the folowing expression to auto create table in my sink table.

This is the current expression:

This expression can truncate the existing SQL Schema and SQL table. But when i need to make new schema and table the Copy Data Activity failed. What change do i need to make in the current expression?

**

@{if(and(not(empty(pipeline().parameters.SQLTargetSchemaName)), 
not(empty(pipeline().parameters.SQLTargetTableName))),
 concat('truncate table [', pipeline().parameters.SQLTargetSchemaName, '].[', pipeline().parameters.SQLTargetTableName, ']'), '')
}

**

This is the current config of my Sink page :

Answer 1

Use the below query in the pre-copy script to achieve your requirement.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '@{pipeline().parameters.tablename}')
begin
    TRUNCATE table [@{pipeline().parameters.schema}].[@{pipeline().parameters.tablename}];
end

And make sure you select the Auto create table in the copy activity sink.

Here, our pre-copy script checks whether the table exists or not, if exists it truncates the table. If not exists, it won't do anything.

The Auto create table creates new table only when the table is not exists in the schema, if it already exists it won't do anything.

Here, my table already exists with one row, so it truncated the table and inserted values from the source.