SQLite Forum

cli: .mode insert - use current table name
Login

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 [link] [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 [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