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
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).
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.
ha.db database. If you have further issues with this, please ask a new question.