How build an insert query with N rows? (Bulk/batch insert)

[enheit]

I have a table appointments which relates to appointment_services (1-to-N). An appointment contains multiple services.

I want to construct 2 insert queries and execute them in a single transaction. The first query will insert an appointment into the appointments table and the second insert query should insert all services related to that appointment.

As far as I understand, the bulk insert is what is needed for the second query.

INSERT INTO (col1, col2, col3) VALUES
 ($1, $2, $3),
 ($3, $4, $5),
 ($6, $7, $8),
 ...
 ($N, $N+1, $N+2)

How to construct such insert query using sea-query API? I was able to find only .values and .values_panic methods. But it seems like they work only for a single row.

---

And the second part of the question. As far as I know, there are two approaches how to insert multiple rows into a table:

1. Insert multiple queries (query per row) under a single transaction
2. Insert one query with multiple row values.

The second option looks more performant for me, but as far as I newbie in this topic I can be wrong.

What approach is more suitable for sea-query? Maybe I am naive and the bulk insert is not a best practice in the query builder world.

[billy1624]

Hey @enheit, welcome!

A single insert statement with multiple rows of value is more performant. To construct such insert statement, you need to call either values / values_panic methods multiple times. See below for example :)

sea-query/tests/mysql/query.rs

Lines 1057 to 1078 in eb5705e

	#[test]
	#[allow(clippy::approx_constant)]
	fn insert_3() {
	assert_eq!(
	Query::insert()
	.into_table(Glyph::Table)
	.columns([
	Glyph::Image,
	Glyph::Aspect,
	])
	.values_panic(vec![
	"04108048005887010020060000204E0180400400".into(),
	3.1415.into(),
	])
	.values_panic(vec![
	Value::String(None),
	2.1345.into(),
	])
	.to_string(MysqlQueryBuilder),
	"INSERT INTO `glyph` (`image`, `aspect`) VALUES ('04108048005887010020060000204E0180400400', 3.1415), (NULL, 2.1345)"
	);
	}

[enheit]

How can I do this dynamically? This example is "bounded" to the known amount of rows - 2. What if I have a vec of rows that can be any size?

As I described above, appointment can contain N services, selected by a user. How to create a query with N rows?

Update:

Oh, wait. Am I able to do something like this?

let insert_query = Query::insert() 
             .into_table(Glyph::Table) 
             .columns([ 
                 Glyph::Image, 
                 Glyph::Aspect, 
             ]);

for service in services.into_iter() {
    insert_query.values_panic(vec![...service]);
} 

insert_query.build(PostgresQueryBuilder);

[billy1624]

Hey @enheit, sorry for the delay. Please check an example at https://github.com/SeaQL/starfish-ql/blob/268e1e9cb1b638b96e59bcb60f708df1af57061f/starfish/starfish-core/src/mutate/node.rs#L64-L96