Copy table structure to new table in sqlite3

问题:

Is there an easy way to copy an existing table structure to a new one? (dont need the data, only the structure -> like id INTEGER, name varchar(20) ...)

Thx

回答1:

You could use a command like this:

CREATE TABLE copied AS SELECT * FROM mytable WHERE 0

but due to SQLite's dynamic typing, most type information would be lost.

If you need just a table that behaves like the original, i.e., has the same number and names of columns, and can store the same values, this is enough.

If you really need the type information exactly like the original, you can read the original SQL CREATE TABLE statement from the sqlite_master table, like this:

SELECT sql FROM sqlite_master WHERE type='table' AND name='mytable'

回答2:

SQLite cannot clone table with PK, defaults and indices.

Hacking by another tool is necessary.

In shell, replace the table name by sed.

sqlite3 dbfile '.schema oldtable' | sed '1s/oldtable/newtable/' | sqlite3 dbfile

And you can check new table.

sqlite3 dbfile '.schema newtable'

Primary key, defaults and indices will be reserved.

I hope this command can help you.

回答3:

sqlite> .schema

CREATE TABLE [About](
  [id],
  [name],
  [value]);

.schema command will give you structure of About-table how it could be made by programming SQLite interpreter by hand, typing in commands.

Paste in and execute, the CREATE block giving the table new name:

sqlite> CREATE TABLE [AboutToo](
  [id],
  [name],
  [value]);

.tables command now will show you have two tables, old and new, "copied".

sqlite> .tables
About     AboutToo

p.s. sqlite> is command prompt you get in console after launching SQLite.exe interpreter. To get it go to www.sqlite.org

回答4:

I would prefer :

> sqlite3 <db_file>

sqlite3 > .output <output_file>
sqlite3 > .dump <table_name>

The line above generates the dump of table that includes DDL and DML statement.

Make changes in this file, i.e. find and replace the table name with new table name

Also, replace "CREATE TRIGGER " with "CREATE TRIGGER <NEWTABLE>_" , this will replace existing triggers with trigger names with a new table name on it. That will make it unique and will not cause conflicts with existing triggers. Once all schema changes are implemented, read it back into database using .read

sqlite3 > .read output_file

This can be scripted in shell file using shell commands like :

echo ".dump <table>" | sqlite3 <db_file> > <table_file>
sed -i.bak "s/\b<table_name>\b/<new_table_name>/g" <table_file>
sed -i.bak "s/\bCREATE TRIGGER \b/CREATE TRIGGER <new_table_name_>/g" <table_file>
echo ".read <table_file>" | sqlite3 <db_file>
rm <table_name>.bak 

For example :

If you have table T and new table is TClone in db file D with file F to be created : then

echo ".dump T" | sqlite3 D.sqlite > F
sed -i.bak "s/\bT\b/TClone/g" F
sed -i.bak "s/\bCREATE TRIGGER \b/CREATE TRIGGER TClone_>/g" F
echo ".read F" | sqlite3 D.sqlite 
rm T.bak

Finally, you can generalize this script by creating a parameterized version where you can pass source_table, destination_table , db_file as parameters that can be used to clone any table.

I tested this and it works.

Testing :

sqlite3 <database_file>
sqlite3 > select * from <new_table>;

should give you same results as original table. and

sqlite3 > .schema <new_table>

should have same schema as that of original table with a new name.