SQLite Forum

.dump only one table with all indexes in the command-line shell
Login

.dump only one table with all indexes in the command-line shell

(1) By anonymous on 2021-01-27 09:11:39 [link] [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 [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