Copy Table Structure to New Table in SQLite3

Copy table structure to new table in sqlite3

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'

How to copy data from one table to another (where both have other fields too that are not in common)?

INSERT INTO Destination SELECT * FROM Source;

See SQL As Understood By SQLite: INSERT for a formal definition.

How to copy data between two tables in SQLite?

Copying in SQL works like so:

insert into table2 (name, address)
select name, address
from table1

If the values of the column id_ are the same, you need to insert and update

insert into table2 (name, address)
select name, address
from table1 t1
where not exists (select * from table2 t2 where t1._id = t2._id)
;
update table2 t2 name = (select name from table1 t2 where t1._id = t2._id)
;
update table2 t2 address = (select address from table1 t2 where t1._id = t2._id)

If you need to copy the columns between databases, you first export them into a file (use any format you like, for example CSV) and then merge that file into the second database manually since you can't write an SQL which says "use these sqlite structures".

Copy Data from one Sqlite table to another Sqlite table

A INSERT statement needs to be specific. I mean using '*' like you can do in a SELECT statement does not work for INSERT (or UDPATE for that matter).

You need to specify the columns one by one or insert the data in the exact same order the table was created.
So for example, let's say your Messages table looks like:

CREATE TABLE Messages(
   ID INT PRIMARY KEY     NOT NULL,
   POSTERNAME     TEXT    NOT NULL,
   MESSAGE        CHAR(500)     NOT NULL
);

When you insert data in this table you can use an INSERT statement like this:

INSERT INTO Messages (ID,POSTERNAME,MESSAGE) 
       VALUES (1,'SomeGuy','SomeMessage');

or use the short syntax:

INSERT INTO Messages VALUES (1,'SomeGuy','SomeMessage');

in your code you could use this like:

using (SQLiteConnection connect = new SQLiteConnection(mainDbPath))
{
    connect.Open();
    using (var transaction = connect.BeginTransaction())
    {
        foreach (DataRow row in secondTable.Rows)
        {
            var cmdText= string.Format("INSERT INTO Messages VALUES ({0},'{1}','{2}');",
                row["ID"], row["POSTERNAME"], row["MESSAGE"]);

            using (var cmd = new SQLiteCommand(cmdText, connect, transaction))
            {
                cmd.ExecuteNonQuery();
            }
        }

        transaction.Commit();
    }
}

Notice that I changed the order of the usings. You probably want to commit the transaction only when all records have been succesfully added to the database. SO first start the transaction before you create the command.

You can do this a lot simplier by using a SELECT statement in the INSERT like this:

INSERT INTO SECONDTABLE SELECT COL1,COL2,COL3 FROM FIRSTTABLE

You offcourse need to replace with proper table and column names.

sqlite3 python ATTACH DATABASE copy table .schema

Inspired by the answer of @CL. , the full code is:

conn = sqlite3.connect(os.path.join("data", "db", "Kanji-story.db"))
c = conn.cursor()
c.execute("DROP TABLE IF EXISTS current")

c.execute("ATTACH DATABASE ? AS db2", (os.path.join('data', 'db', 'Kanji-story_bak.db'),))

c.execute("SELECT sql FROM db2.sqlite_master WHERE type='table' AND name='current'")
c.execute(c.fetchone()[0]) # Contains: CREATE TABLE current (framenum INTEGER, nextKanji INTEGER)
c.execute("INSERT INTO main.current SELECT * FROM db2.current")

conn.commit()
conn.close()

Create table (structure) from existing table

Try:

Select * Into <DestinationTableName> From <SourceTableName> Where 1 = 2

Note that this will not copy indexes, keys, etc.

If you want to copy the entire structure, you need to generate a Create Script of the table. You can use that script to create a new table with the same structure. You can then also dump the data into the new table if you need to.

If you are using Enterprise Manager, just right-click the table and select copy to generate a Create Script.

---

Related Topics