.dump only one table with all indexes in the command-line shell
(1) By anonymous on 2021-01-27 09:11:39 [source]
-- create 2 tables with index
CREATE TABLE t1 ( a );
INSERT INTO t1 VALUES ( 'aaa' );
INSERT INTO t1 VALUES ( 'bbb' );
INSERT INTO t1 VALUES ( 'ccc' );
CREATE INDEX t1_a ON t1 ( a );
CREATE TABLE t2 ( b );
INSERT INTO t2 VALUES ( 'xxx' );
INSERT INTO t2 VALUES ( 'yyy' );
INSERT INTO t2 VALUES ( 'zzz' );
CREATE INDEX t2_b ON t2 ( b );
-- dump the whole database works perfect
.dump
-- dump only table t1
.dump t1
--> create index at the end is missing
--
-- Question: How can i dump only one table with all indexes?
--
-- The only solution i have found is the following:
.schema t1
.dump t1
-- Problem:
-- Two CREATE TABLE statements
-- and the CREATE INDEX command is before the INSERT commands
(2) By anonymous on 2021-01-27 12:29:17 in reply to 1 [link] [source]
try
.schema yourtableName
(3) By anonymous on 2021-01-27 14:34:11 in reply to 2 [link] [source]
Thanks but this only creates the table and the index. No insert is made. In my example it will create the following:
sqlite> .schema t1
CREATE TABLE t1 ( a );
CREATE INDEX t1_a ON t1 ( a );
The problem is this:
The command .dump
creates all commands to restore the database with all indexes.
But I want to do this for only one table. Copy one table into another database:
sqlite3 database.sqlite ".dump t1" | sqlite3 database2.sqlite
The command .dump t1
do this. But in the output the create index command is missing.
A workaround are the following two commands:
sqlite3 database.sqlite ".dump t1" | sqlite3 database2.sqlite
sqlite3 database.sqlite ".schema t1" | sqlite3 database2.sqlite
Is this the only way to copy a table with index into another database?
(4) By anonymous on 2021-01-27 18:12:44 in reply to 3 [link] [source]
Copy one table into another database:
sqlite> attach database 'your_COPY_DatabaseName' as cpy;
sqlite> create table cpy.t1 as select * from t1;
But in the output the create index command is missing.
You can retrieve the script for the index as:1
sqlite> select sql from sqlite_master where tbl_name = 't1' and type = 'index';
sql
-----------------------------
CREATE INDEX t1_a ON t1 ( a )
sqlite>
Then
1 open your_COPY_DatabaseName
2 apply the script returned1
I can't readily think of a way to automate this.
(5) By anonymous on 2021-01-27 21:30:15 in reply to 4 [link] [source]
Thank you that is also a possibility.
I have just compiled an old version:
[glaeser@HAL-9000 sqlite-amalgamation-3080704]$ ./my_sqlite3 xxx.db
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> .ta
t1 t2
sqlite> .q
[glaeser@HAL-9000 sqlite-amalgamation-3080704]$ ./my_sqlite3 xxx.db ".dump"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 ( a );
INSERT INTO "t1" VALUES('aaa');
INSERT INTO "t1" VALUES('bbb');
INSERT INTO "t1" VALUES('ccc');
CREATE TABLE t2 ( b );
INSERT INTO "t2" VALUES('xxx');
INSERT INTO "t2" VALUES('yyy');
INSERT INTO "t2" VALUES('zzz');
CREATE INDEX t1_a ON t1 ( a );
CREATE INDEX t2_b ON t2 ( b );
COMMIT;
[glaeser@HAL-9000 sqlite-amalgamation-3080704]$ ./my_sqlite3 xxx.db ".dump t1"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 ( a );
INSERT INTO "t1" VALUES('aaa');
INSERT INTO "t1" VALUES('bbb');
INSERT INTO "t1" VALUES('ccc');
CREATE INDEX t1_a ON t1 ( a );
COMMIT;
[glaeser@HAL-9000 sqlite-amalgamation-3080704]$ ./my_sqlite3 xxx.db ".dump t2"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t2 ( b );
INSERT INTO "t2" VALUES('xxx');
INSERT INTO "t2" VALUES('yyy');
INSERT INTO "t2" VALUES('zzz');
CREATE INDEX t2_b ON t2 ( b );
COMMIT;
In this version the output is as expected with index...
With the current version it only works like this:
.schema t1
.dump t1 --data-only