SQL Server中没有MySQL中的REPLACE INTO语句,可使用不存在时插入数据,存在时更新数据的方法来实现
SQL Server不存在时插入数据(insert when/where not exists)
REPLACE INTO 实现逻辑
INSERT INTO table (id, name)
SELECT * FROM ( VALUES
(1,'name1'),
(2,'name2')
) AS table_temp (id, name)
WHERE NOT EXISTS (
SELECT * FROM table WHERE table.id= table_temp.id
)
REPLACE INTO 实现
<insert id="insertListMssql">
INSERT INTO mch_info(<include refid="Base_Column_List"/>)
SELECT * FROM ( VALUES
<foreach collection="list" index="i" separator="," item="item">
(
#{item.mchId},
#{item.mchName},
)
</foreach>
) AS mch_info_temp (<include refid="Base_Column_List"/>)
WHERE NOT EXISTS (SELECT 1 FROM mch_info WHERE mch_info.mchId = mch_info_temp.mchId)
</insert>
<insert id="updateListMssql">
UPDATE mch_info SET
mch_info.mchName = mch_info_temp.mchName
FROM mch_info ,(SELECT * FROM ( VALUES
<foreach collection="list" index="i" separator="," item="item">
(
#{item.mchId},
#{item.mchName}
)
</foreach>
) AS temp (<include refid="Base_Column_List"/>)) AS mch_info_temp
WHERE mch_info.mchId = mch_info_temp.mchId
</insert>