New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Extend INSERT to support inserting multiple rows in single command (ala PostgreSQL/MySQL) [CORE1978] #2416
Comments
Commented by: @aafemt In ISQL it is already implemented as bulk_insert. In DSQL it is less effective than array DML. What's the point? |
Commented by: Gustavo Torres (gusta1308) I want an easy way for to send in one transaction multiples rows, around 500 rows or less. |
Commented by: @livius2 this is already implemented feature execute block end |
Commented by: @dyemanov Karol, the requested multi-value INSERT statement is a standard feature, so I believe the request is perfectly valid regardless of our EXECUTE BLOCK feature (which can be used as a temporary workaround though). |
Commented by: Sean Leyne (seanleyne) Edited the subject to correct terminology (rows vs. values), clarify requirement (single command vs. complex SELECT UNION/Inline PSQL [i.e. EXECUTE BLOCK solution] as well as identifying that the engines which support the single command approach are PostgreSQL and MySQL (MS SQL does not support at all, Oracle has an odd variation). |
Modified by: Sean Leyne (seanleyne)description: I suggeste include Insert multi-values INSERT INTO Table (a, b, c) for improvement performance insert => I suggest include INSERT for multi-values INSERT INTO Table (a, b, c) for improvement performance insert summary: INSERT with multi-values => Extend INSERT to support inserting multiple rows in single command (ala PostgreSQL/MySQL) |
Commented by: Eli Godoy (agamenon) I have 200-250 fields, I need to put ALL fields in each insert? |
Commented by: Stefan Heymann (stefanheymann) > What's the point? |
Commented by: Tommi Prami (mwaltari) I think this would be very good. Less stuff top write when inserting from some text file etc. No need to repeat INSERT INTO Table (a, b, c) for all rows. Quite old request most likely missed the train for FB4.0 already, maybe next bigger release. |
Modified by: @asfernandesassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Commented by: @dyemanov At least they should share the same underlying implementation (constant multi-row record source), I'd say. |
Commented by: @pavel-zotov Gustavo, there is undocument command in ISQL, its syntax is like this: set bulk_insert [ update or ] insert into ... [ matching (...) ] It runs more than 3x faster than if you will try to use prepared statement and loop with passing of parameters for each row (I compared using Python FDB driver, cursor.executemany( [data_array] ); maybe for other drivers this ratio will differ). Here is example:set bulk_insert update or insert commit;NB-1: "set bulk_insert update or insert" - must be written in one line PS Note also, that connection using xnet or wnet protocol will run significantkly faster than using inet. For text file with size ~53 Mb (1'080'980 rows) i got: |
Commented by: Sean Leyne (seanleyne) Pavel, A "Documentation" ticket/issue should be created for this "hidden" feature to be documented. Separately, does command syntax the full standard INSERT statement syntax for columns/fields [i.e. GEN_ID(), CAST and (SELECT xxx From yyyy)]? |
Commented by: @asfernandes It's a incomplete hack, it's ISQL only. It has been created only for internal messages scripts. Better to maintain it undocumented. |
Submitted by: Gustavo Torres (gusta1308)
Votes: 20
I suggest include INSERT for multi-values
INSERT INTO Table (a, b, c)
values(1, 2, 3), (4, 5, 6);
for improvement performance insert
The text was updated successfully, but these errors were encountered: