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
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
I would prefer :
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
This can be scripted in shell file using shell commands like :
For example :
If you have table T and new table is TClone in db file D with file F to be created : then
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 :
should give you same results as original table. and
should have same schema as that of original table with a new name.
sqlite> .schema
.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:
.tables command now will show you have two tables, old and new, "copied".
p.s. sqlite> is command prompt you get in console after launching SQLite.exe interpreter. To get it go to www.sqlite.org
You could use a command like this:
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 thesqlite_master
table, like this:SQLite cannot clone table with PK, defaults and indices.
Hacking by another tool is necessary.
In shell, replace the table name by sed.
And you can check new table.
Primary key, defaults and indices will be reserved.
I hope this command can help you.