DSXchange

What is the main difference in using the the user defined sql and a regular insert statement. I was debugging a job that wasn't working as expected and noticed that the developer had the write mode set to 'USER DEFINED SQL' for a very basic insert using job parameters. While using the same insert sql I changed the write mode to insert and noticed that the sql started acting as we though it should. Are there any under-lying differences between the two write modes?

SQL
*************************************************************
INSERT INTO #DATAREP_PARAMS.DATA_SCHEMA#.#TABLE_ROOT#_NORM_BLOCKED
(INSERTED, LSSI_RECID, LAST_REFRESHED,SUPPLIER_REGION_NUM, REASON) VALUES
('#PROCESS_START#', ORCHESTRATE.LSSI_RECID, '#PROCESS_START#', #REGION_NUM#, ORCHESTRATE.MATCH_CODE)
*************************************************************

Hi wblack,

By using a user defined SQL-Statement you can enter some additional logic into update-statements if needed.

For inserts I can't think of any reason to use user-defined SQL. I would personally prefer to use a transformer to add the Job parameters and let the database-stage generate the insert statement itself. It makes the job easier to maintain, because you don't have to change the SQL to add additional columns to the target-table later.