cli: .mode insert - use current table name
(1) By ThanksRyan on 2021-05-03 17:55:17 [link] [source]
Hello,
I really like the .mode insert
when using the command line interface, so that you can give a sample data that can be easily inserted into an already known schema to someone else's database.
However, when you don't specify new_table, it defaults to table
in the output.
Is it possible to have the insert mode print the current table you're selecting from?
CREATE TABLE IF NOT EXISTS
"funwithsqlite" (
"ID" ,
"heading2",
"heading3"
);
sqlite> .mode insert
sqlite> select * from funwithsqlite limit 5;
INSERT INTO "table" VALUES(2,6400,'2021-05-07');
INSERT INTO "table" VALUES(3,9700,'2021-05-10');
INSERT INTO "table" VALUES(4,6500,'2021-05-08');
INSERT INTO "table" VALUES(5,1400,'2021-05-12');
INSERT INTO "table" VALUES(6,7200,'2021-05-13');
Can table
above be replaced with funwithsqlite
?
If not, I respectfully request this as a feature request for the cli.
I see the .dump
uses the current table name, but it looks like it dumps the entire database and not a partial amount.
Thanks!
(2) By Larry Brasfield (larrybr) on 2021-05-03 18:04:23 in reply to 1 [link] [source]
Try:
.mode insert funwithsqlite
select * from funwithsqlite;
(3) By ThanksRyan on 2021-05-03 18:10:32 in reply to 2 [source]
Larry,
Very nice!!
It was my mistake for not attempting funwithsqlite as the argument. I mistakenly thought it would duplicate the data or override the existing table somehow.
Thanks for the fast response!
(4) By Keith Medcalf (kmedcalf) on 2021-05-03 19:39:23 in reply to 3 [link] [source]
The tablename argument to .mode insert tablename
is merely an annotation (the default if not specified is 'table') to be used in the .mode insert
output. For example, in your case:
CREATE TABLE IF NOT EXISTS
"funwithsqlite" (
"ID" ,
"heading2",
"heading3"
);
sqlite> .mode insert funwithsqlite_copy
sqlite> select * from funwithsqlite limit 5;
INSERT INTO "funwithsqlite_copy" VALUES(2,6400,'2021-05-07');
INSERT INTO "funwithsqlite_copy" VALUES(3,9700,'2021-05-10');
INSERT INTO "funwithsqlite_copy" VALUES(4,6500,'2021-05-08');
INSERT INTO "funwithsqlite_copy" VALUES(5,1400,'2021-05-12');
INSERT INTO "funwithsqlite_copy" VALUES(6,7200,'2021-05-13');
(5) By ThanksRyan on 2021-05-04 03:35:47 in reply to 4 [link] [source]
Excellent explanation. Thank you!
(7) By Keith Medcalf (kmedcalf) on 2021-05-04 06:47:34 in reply to 5 [link] [source]
Technically the .mode insert tablename
is the same as .mode quote
with the difference being that each line/row is prefaced with insert into "tablename" values(
and then postfixed with );
where the "tablename" comes from the .mode insert
command line.
(6) By Gunter Hick (gunter_hick) on 2021-05-04 06:32:41 in reply to 1 [link] [source]
What if the SELECT is a join? Which table should be chosen? I find it very practical when performing an INSERT INTO ... SELECT where source and destination tables reside on different machines or even distinct DBMS. .mode insert oracletable .out ORA_IMPORT.SQL SELECT ... sqlplus ... <ORA_IMPORT.SQL