Copy table structure to new table in sqlite3

by

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"

More Related Contents:

  1. How can I list the tables in a SQLite database file that was opened with ATTACH?
  2. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  3. Deleting duplicate rows from sqlite database
  4. SQLite – How do you join tables from different databases?
  5. Does SQLite3 not support foreign key constraints?
  6. SQLite – ORDER BY RAND()
  7. How to get a list of column names on Sqlite3 database?
  8. Drop all tables command
  9. Core Data Performance with Single Parent Entity
  10. Android multiple databases open
  11. sqlite select with condition on date
  12. SQLite3 UNIQUE constraint failed error
  13. What are the options for storing hierarchical data in a relational database?
  14. How to pivot in SQLite or i.e. select in wide format a table stored in long format?
  15. Is there any boolean type in Oracle databases?
  16. A beginner’s guide to SQL database design [closed]
  17. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  18. The Next-gen Databases [closed]
  19. Do database transactions prevent race conditions?
  20. sqlite alter table add MULTIPLE columns in a single statement
  21. How to sum up time field in SQL Server
  22. How to show row numbers in PostgreSQL query?
  23. Does SQLite3 have prepared statements in Node.js?
  24. How to do an INNER JOIN on multiple columns
  25. Using SQL Server as a DB queue with multiple clients
  26. SQL 2005 – The column was specified multiple times
  27. Upsert with a transaction
  28. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  29. Relational Data Model for Double-Entry Accounting
  30. Do conditional INSERT with SQL?

Leave a Comment