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
This tasks merges data from a source table to a target table.
|@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.
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