Merging Data

EDIS Online User Guide – Merging Data

Merging data with EDIS is fast and easy. You never have to write an insert/update/delete statement again after using this task

Merge Task
This tasks merges data from a source table to a target table.

Procedure: SSISDB.EDIS.usp_merge

Parameters

Name Description Default Value
@target Target table to merge the data to. If the target is not a temp table, then it needs to be fully qualified i.e. [Database].[schema].[table]. For temp tables you can just specify the table name i.e. “#sales_data”. None; Required
@source Source table to provide data to merge to the target table. If the source is not a temp table, then it needs to be fully qualified i.e. [Database].[schema].[table]. For temp tables you can just specify the table name i.e. “#sales_data”. None; Required
@delete_no_match Flag indicating that if a row in the target does not exist in the source based on joining on the primary key, then the target row will be deleted. 0 (False)
@allow_identity_insert Flag indicating if you want to enable an insert of a value on an identity column. 0 (False)
@target_sub_query A merge statement can merge to a subset of a table using the WITH clause. This parameter mimics this in that when you specify a target sub query, it will encapsulate it in the WITH clause and merge against it. This parameter is very beneficial for large tables where you only need to change a subset of rows in the table. Empty String
@target_key_cols If you are merging against a target table that does not have a primary key, you need to specify the key columns in this parameter so that the merge command knows what to join the source and target on. If the target has multiple key columns, separate them with a comma i.e. “@target_key_cols = “loc_nbr, wk” Empty String
@print_merge_statement Flag indicating if you want to print the merge statment that gets created for execution 0 (False)
@rows_affected Output variable of total rows affected from the merge statement. Integer – OUT
@rows_inserted Output variable of total rows inserted from the merge statement. Integer – OUT
@rows_updated Output variable of total rows updated from the merge statement. Integer – OUT
@rows_deleted Output variable of total rows deleted from the merge statement. Integer – OUT

Example: This example merges data from temp table #sales to target SALES_REP.dbo.sales_wkly_stats.

EXEC SSISDB.EDIS.usp_merge

 @target = 'SALES_REP.dbo.sales_wkly_stats'

,@source = '#sales'

 

Example 2: This example merges data from temp table #sales to target SALES_REP.dbo.sales_wkly_stats, but also deletes rows in the target table that do not exist in the source. It also captures the total rows affected, inserted, updated, and deleted, and prints them

DECLARE @rows_a int, @rows_i int, @rows_u int, @rows_d int;

EXEC SSISDB.EDIS.usp_merge

 @target = 'SALES_REP.dbo.sales_wkly_stats'

,@source = '#sales'

,@delete_no_match = 1

,@rows_affected = @rows_a OUT

,@rows_inserted = @rows_i OUT

,@rows_updated = @rows_u OUT

,@rows_deleted = @rows_d OUT

PRINT @rows_a

PRINT @rows_i

PRINT @rows_u

PRINT @rows_d