CODEWITHSUNDEEP
javasql-servertable-valued-parameters
Ayush
Ayush

Reputation: 19

Use Table Valued Parameter in Merge sql statement

I am trying to use sql server's MERGE command. However, I am not sure, how do we pass it as a parameter using Java.

I am setting up SqlParamSource as

private SqlParameterSource getSqlParamsForPollingLogUpdateWithMerge(int[] ids){
        final MapSqlParameterSource params = new MapSqlParameterSource();
        try{
            SQLServerDataTable table = new SQLServerDataTable();
            table.addColumnMetadata("id",java.sql.Types.INTEGER);
            table.setTvpName("dbo.idtable");
            for (int id : ids) {
                table.addRow(id);
            }

            params.addValue("idtable", table);
            return params;
        }catch (Exception ex){
            throw new RuntimeException(ex);
        }

    }

My sql looks like:

String sql= "create type dbo.idtable AS TABLE (id INT);" +
                        "MERGE INTO table1 AS tgt" +
                "USING :idtable as src\n" +
                "ON tgt.id=src.id" +
                "WHEN MATCHED THEN" +
                "UPDATE SET ...<fewupdates here>" +
                "WHEN NOT MATCHED THEN" +
                "INSERT <inserts here>;";

The problem that I am facing is the data type 'dbo.idtable' is not getting resolved. I am unsure how to proceed now.

Upvotes: -1

Views: 75

Answers (1)

Ayush
Ayush

Reputation: 19

Using OpenJson worked for me. This is how sql looks like now. :json is the json array having multiple json records with one id key.

 "MERGE INTO table1 AS tgt \n" +
                "USING openjson(:json) with (id int '$.id') as src \n" +
                "ON tgt.id=src.id " +
                "WHEN MATCHED THEN " +
                "UPDATE SET <> " +
                "WHEN NOT MATCHED THEN " +
                "INSERT <>;"

Upvotes: 0

Related Questions