Use Table Valued Parameter in Merge sql statement

Question

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.

I'm not sure it works like this. You have to create the type ahead of time once. Also, a bit unsure you can bind a merge source as parameter like that. — siggemannen
– siggemannen, Commented Sep 24, 2024 at 12:56
Considering the problem you're asking about is how to bind the TVF from Java, tagging [java] seems important here. — Thom A
– Thom A ♦, Commented Sep 24, 2024 at 13:15
You certainly will need the type to be created before trying to submit a batch with a parameter of that type. I rarely have anything to do with Java so can't comment on the rest of it — Martin Smith
– Martin Smith, Commented Sep 24, 2024 at 13:22
If you can't or don't want to create the table type ahead of time, you can pass a JSON string and use OPENJSON to parse it in TSQL. Or you can't or don't want to figure out how to use TVPs in JDBC. learn.microsoft.com/en-us/sql/connect/jdbc/… — David Browne - Microsoft
– David Browne - Microsoft, Commented Sep 24, 2024 at 14:56
OPENJSON() is faster and scales much better than TVPs. Superior in just about every way. — StrayCatDBA
– StrayCatDBA, Commented Sep 24, 2024 at 15:30

Ayush · Accepted Answer · 2024-09-25 06:11:04Z

0

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 <>;"

answered Sep 25, 2024 at 6:11

Ayush

195 bronze badges

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

Collectives™ on Stack Overflow

Use Table Valued Parameter in Merge sql statement

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