8 ms·

I find SQL INSERT statement not intuitive. I can understand why SQL requires me to declare the field names and then the values of a new row that I am inserting;

by homami 6y ago

I find SQL INSERT statement not intuitive. I can understand why SQL requires me to declare the field names and then the values of a new row that I am inserting; but it would've been a huge time saver if SQL had a key-value dictionary-like syntax: INSERT INTO "my_table" "col1": value1, "col2": value2, ...

PZ81JUXJE7uJ 6y ago
MySQL supports a insert/set statement: INSERT INTO tbl SET a = 1, b = 2;
- castorp 6y ago
  How does that look like with a multi-row insert?
castorp 6y ago
And when you need to insert more than one row, you repeat the column name over and over again for each row - not a good idea either.
- rspeele 6y ago
  Single-row inserts are super common in both application code and in interactive usage of SQL, so I think that it is worth it to have a syntax for them that reduces this common error. Especially when a table has many columns of the same type (like booleans). E.g. insert into Permissions(UserId, Create, Read, Update, Delete, Share, ForceUnlock, LaunchNukes) values (12345, 1, 1, 0, 1, 0, 0, 1); When I wrote my (now unmaintained) statically typed SQL dialect for F#, which compiles to underlying SQLite/Postgres/MSSQL, I added a single-row insert with Field=Value because it's nice to have and took no more than 30 minutes to do. It's only a tweak to the parser after all -- you just parse it to the same AST used to represent the `INSERT ... VALUES` clause and all later stages of the compilation do not need to know about it. https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertStmt.html https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertSt...
  - magicalhippo 6y ago
    The DB we use at work (SQLAnywhere) supports inserting from select, and using auto column name matching, so for a table with columns (name, en1, en2) you could have INSERT INTO tbl WITH AUTO NAME SELECT 'foo' AS name, 1 AS en1, 0 AS en2; Not nearly as neat as the direct key=value syntax but...
- deleted 6y ago
  [deleted]

by homami 6y ago

PZ81JUXJE7uJ 6y ago

MySQL supports a insert/set statement: INSERT INTO tbl SET a = 1, b = 2;

castorp 6y ago
How does that look like with a multi-row insert?

castorp 6y ago

And when you need to insert more than one row, you repeat the column name over and over again for each row - not a good idea either.

rspeele 6y ago
Single-row inserts are super common in both application code and in interactive usage of SQL, so I think that it is worth it to have a syntax for them that reduces this common error. Especially when a table has many columns of the same type (like booleans). E.g. insert into Permissions(UserId, Create, Read, Update, Delete, Share, ForceUnlock, LaunchNukes) values (12345, 1, 1, 0, 1, 0, 0, 1); When I wrote my (now unmaintained) statically typed SQL dialect for F#, which compiles to underlying SQLite/Postgres/MSSQL, I added a single-row insert with Field=Value because it's nice to have and took no more than 30 minutes to do. It's only a tweak to the parser after all -- you just parse it to the same AST used to represent the `INSERT ... VALUES` clause and all later stages of the compilation do not need to know about it. https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertStmt.html https://rspeele.gitbooks.io/rezoom-sql/doc/Language/InsertSt...
- magicalhippo 6y ago
  The DB we use at work (SQLAnywhere) supports inserting from select, and using auto column name matching, so for a table with columns (name, en1, en2) you could have INSERT INTO tbl WITH AUTO NAME SELECT 'foo' AS name, 1 AS en1, 0 AS en2; Not nearly as neat as the direct key=value syntax but...
deleted 6y ago
[deleted]