-2

I want to write a shell script that firstly opens sqlite and then makes a copy of a table and finally deletes the contents of that table. How can I do it in a shell script?

>>cd /var/www/dbs
>>sqlite3 ha.db
sqlite>>.timeout 2000
sqlite>>INSERT INTO table1 SELECT * FROM table2;
sqlite>>DELETE * FROM table2;
sqlite>>.quit

How to write this part?

>>sqlite3 ha.db
sqlite>>INSERT INTO table1 ('a1','a2') VALUES ('1','2');
sqlite>>.quit
1
cd /var/www/dbs
echo ".timeout 2000
INSERT INTO table1 SELECT * FROM table2;
DELETE * FROM table2;
.quit" |
    sqlite3 ha.db

This only works if sqlite3 reads from stdin and not from its tty.

  • Hi, I want to insert into a table in a shell script. When I write it as: echo "INSERT INTO table1 ('a1','a2') VALUES ('1','2'); .quit" | sqlite3 ha.db ,it issues errors that a1 and a2 are not found.What is wrong? and how can I write this? – Ahmad Fatehi May 19 '18 at 11:48
  • 1
    @AhmadFatehi This is a new question that we can't answer unless we somehow also know the database schema for your ha.db database. If you have further issues with this, please ask a new question. – Kusalananda May 19 '18 at 12:18
1

sqlite3 reads commands from standard input, which means that you may feed it SQL from a file or from the command line and not just interactively. sqlite3 will automatically exit at the end of input, so .quit is not necessary when using it non-interactively.

For example:

#!/bin/sh

sqlite3 /var/www/dbs/ha.db <<END_SQL
.timeout 2000
INSERT INTO table1 SELECT * FROM table2;
DELETE FROM table2;
END_SQL

Also notice the corrected syntax for the DELETE.

If you also need to create table2 in the database:

#!/bin/sh

sqlite3 /var/www/dbs/ha.db <<END_SQL
CREATE TABLE IF NOT EXISTS table2 AS SELECT * FROM table1;
INSERT INTO table2 SELECT * FROM table1;
DELETE FROM table1;
END_SQL

Change DELETE FROM table1; to DROP table1; if you need to delete table1 completely (and not just its content).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.