Batch "insert if missing" with SQL server and MyBatis

Question

I have a list of Integers which I want to (batch) insert into an SQL Server table with a single integer column.

The problem is that some of the values being inserted might already exist in the table. Is there a way of performing a batch "insert if missing" into Sql Server and MyBatis?

Malt · Accepted Answer · 2018-10-26 19:36:33Z

1

The following mapper worked for me :

<insert id="batchAddIntegers" parameterType="java.util.List">
    DECLARE @ValuesToInsertTempTable TABLE (ColumnName integer)
    DECLARE @UpdateVariable integer

    SET NOCOUNT ON

    INSERT INTO @ValuesToInsertTempTable (ColumnName) VALUES
    <foreach item="item" index="index" collection="list" open="(" separator="),(" close=")">
        #{item}
    </foreach>

    SET NOCOUNT OFF

    MERGE TargetTable
    USING @ValuesToInsertTempTable AS S
    ON  TargetTable.ColumnName=S.ColumnName
    WHEN NOT MATCHED THEN
        INSERT (ColumnName) VALUES (S.ColumnName)
    WHEN MATCHED THEN
        UPDATE SET @UpdateVariable = @UpdateVariable + 1;
</insert>

edited Oct 26, 2018 at 19:36

answered Mar 2, 2015 at 11:35

Malt

30.5k10 gold badges73 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Batch "insert if missing" with SQL server and MyBatis

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related