BIEE 11g回写插入失败

1、问题：

     BIEE 11g往sqlserver回写插入数据时，插入时没有报错，但是插入操作失败，数据库没有刚插入的数据。但是更新操作是可以的？？？？？

  回写模板WriteBack文件内容如下：

<WebMessage name="SetAccountingRegion">
<XML>
<writeBack connectionPool="VcreditDW Conn">
<insert>INSERT INTO Financial_DivisionMapping (StoreName,LendingSide,AccountDivision,MonthsDivision)
VALUES ('@1',isnull('@2',' '),isnull('@3',' '),isnull('@4',' '))</insert>
<update>UPDATE Financial_DivisionMapping set LendingSide=isnull('@2',' '), AccountDivision=isnull('@3',' '),MonthsDivision=isnull('@4',' ')
where StoreName='@1'</update>
</writeBack>
</XML>
</WebMessage>

2、问题分析：

     查看日志发现执行INSERT回写操作发送的sql语句为UPDATE语句(条件为StoreName=’输入的值‘)，而在数据库中StoreName还没有此条数据，所以插入失败。

3、解决方法：

    修改回写模板WriteBack文件内容如下，即可：

<WebMessage name="SetAccountingRegion">
<XML>
<writeBack connectionPool="VcreditDW Conn">
<insert>MERGE INTO Financial_DivisionMapping A
                USING (SELECT  ltrim(rtrim('@1')) as StoreName
                              ,ltrim(rtrim('@2')) as LendingSide
                              ,ltrim(rtrim('@3')) as AccountDivision
                              ,ltrim(rtrim('@4')) as MonthsDivision
                        ) B
                          ON (A.StoreName=B.StoreName)
              WHEN MATCHED THEN
                   UPDATE
                     set
                         A.LendingSide = B.LendingSide,
                         A.AccountDivision = B.AccountDivision,
                         A.MonthsDivision=B.MonthsDivision
              WHEN NOT MATCHED THEN
                INSERT
                VALUES  
                    (
                       B.StoreName
                      ,B.LendingSide
                      ,B.AccountDivision
                      ,B.MonthsDivision
                    );</insert>
<update>MERGE INTO Financial_DivisionMapping A
                USING (SELECT  ltrim(rtrim('@1')) as StoreName
                              ,ltrim(rtrim('@2')) as LendingSide
                              ,ltrim(rtrim('@3')) as AccountDivision
                              ,ltrim(rtrim('@4')) as MonthsDivision
                        ) B
                          ON (A.StoreName=B.StoreName)
              WHEN MATCHED THEN
                   UPDATE
                     set
                         A.LendingSide = B.LendingSide,
                         A.AccountDivision = B.AccountDivision,
                         A.MonthsDivision=B.MonthsDivision
              WHEN NOT MATCHED THEN
                INSERT
                VALUES  
                    (
                       B.StoreName
                      ,B.LendingSide
                      ,B.AccountDivision
                      ,B.MonthsDivision
                    );
</update>
</writeBack>
</XML>
</WebMessage>

注：在SQLSERVER中MERGE语句后面一定要加分号，在oracle中则不用加分号。