sqlserver批量添加和修改

<insert id="batchInsertPropertyPriceTrend" parameterType="java.util.List">
    begin
    <foreach collection="propertypricetrends" item="item" index="index"  separator=";">
        MERGE INTO [dbo].[PropertyPriceTrend]  us1
        USING
        (
        SELECT '${item.propertyEffectiveId}' PropertyEffectiveId,
        '${item.createTimeStr}' createTime,
        '${item.priceType}' priceType,
        '${item.beforePrice}' beforePrice,
        '${item.updatePrice}' updatePrice,
        '${item.DeleteFlag}' DeleteFlag,
        '${item.CreateUser}' CreateUser,
        '${item.createDateStr}' CreateDate,
        '${item.UpdateUser}' UpdateUser,
        '${item.updateDateStr}' UpdateDate
        ) us2
        ON (
        us1.PropertyEffectiveId=us2.PropertyEffectiveId
        AND DATEDIFF ([second], us1.createTime, us2.createTime)  = 0
        AND us1.priceType = us2.priceType
        AND us1.DeleteFlag = us2.DeleteFlag
        )
        WHEN MATCHED THEN
        UPDATE SET us1.beforePrice = us2.beforePrice, us1.updatePrice = us2.updatePrice
        WHEN NOT MATCHED THEN
        INSERT (PropertyEffectiveId, createTime, priceType, beforePrice, updatePrice, DeleteFlag, CreateUser, CreateDate, UpdateUser, UpdateDate)
        VALUES(us2.PropertyEffectiveId, CONVERT(datetime,us2.createTime), us2.priceType, us2.beforePrice, us2.updatePrice, us2.DeleteFlag, us2.CreateUser, CONVERT(datetime,us2.CreateDate), us2.UpdateUser, CONVERT(datetime,us2.UpdateDate))
    </foreach>
    ;end;
</insert>
复制代码

由于数据库表[dbo].[PropertyPriceTrend]中createTime是datetime类型
传参：集合对象包含了时间,将时间转换yyyy-MM-dd HH:mm:ss时间字符串进行中间处理
sqlserver与mysql还是有很大区别的！