mssql(sql server):batch upsert error... (upsert multiple rows)

[chouqimin]

when I use sql server batch insert and update

CREATE TABLE test_table(keycol CHAR(1) PRIMARY KEY, col1 CHAR(1), col2 CHAR(1));

data := []map[string]interface{}{
		{"keycol": "A", "col1": "1", "col2": "1"},
		{"keycol": "B", "col1": "2", "col2": "2"},
		{"keycol": "C", "col1": "3", "col2": "3"},
		{"keycol": "D", "col1": "4", "col2": "4"},
	}

sql_upsert := `
		MERGE test_table trg
		USING (VALUES (:keycol, :col1, :col2)) src(keycol, col1, col2)
			ON trg.keycol = src.keycol
		WHEN MATCHED THEN
			UPDATE SET col1 = src.col1, col2 = src.col2
		WHEN NOT MATCHED THEN
			INSERT(keycol, col1, col2)
			VALUES(src.keycol, src.col1, src.col2);
		`

_, err := tx.NamedExec(sql_upsert, data)

I got error sql: expected 3 arguments, got 12

when len(data) = 1 is no problem

I want to confirm whether the batch insert of sql server is not yet supported?

[rosehsu47]

same question here 🙏

[chouqimin]

I temporarily solve my problem according to issue 796
Because the regex of sqlx v1.3.5 requires a closing bracket before values
So I added a line of comment --) to my query to temporarily solve my problem

MERGE test_table trg
USING (VALUES (:keycol, :col1, :col2)) src(keycol, col1, col2)
	ON trg.keycol = src.keycol
WHEN MATCHED THEN
	UPDATE SET col1 = src.col1, col2 = src.col2
WHEN NOT MATCHED THEN
	INSERT(keycol, col1, col2)
	VALUES(src.keycol, src.col1, src.col2);

To

MERGE test_table trg
USING (
-- )
VALUES (:keycol, :col1, :col2)) src(keycol, col1, col2)
	ON trg.keycol = src.keycol
WHEN MATCHED THEN
	UPDATE SET col1 = src.col1, col2 = src.col2
WHEN NOT MATCHED THEN
	INSERT(keycol, col1, col2)
	VALUES(src.keycol, src.col1, src.col2);