CODEWITHSUNDEEP
sqldatabasesqlite
Andy
Andy

Reputation: 46334

Combining two tables in sqlite3

I have two tables in two separate sqlite3 databases. The datatypes are identical, but the schemas slightly different. I want them to be a single table in a single database with the same schema as Table 2

Table 1

CREATE TABLE temp_entries (
    id INTEGER PRIMARY KEY, 
    sensor NUMERIC, 
    temp NUMERIC, 
    date NUMERIC);

Table 2

CREATE TABLE "restInterface_temp_entry" (
    "id" integer NOT NULL PRIMARY KEY,
    "dateTime" integer NOT NULL,
    "sensor" integer NOT NULL,
    "temp" integer NOT NULL
);

id is not unique between the two tables. I would like to create another table with the same schema as Table 2. I would like the id for the entries in Table 1 to start from 0 and then the entries from table 2 to start after the last entry from table 1.

Ideally I would like to just add the entries from Table 1 to Table 2 and "reindex" the primary key so that it was in the same ascending order that "dateTime" is.

UPDATE: I now have both tables using the same schema, I did this by creating a new table with the same schema as Table 2 into the database that held Table 1. I than copied the data to the new table with something like:

INSERT INTO restInterface_temp_entry(id,dateTime,sensor,temp)
   ...> select id,date,sensor,temp FROM temp_entries;

Background

I used to record a bunch of temp_entries to a csv file. I wanted to put the data into a format that was easier to work with and chose sqlite3. I wrote a program that pulled all of the entries out and put them into Table 1. I wasn't sure what I was doing at the time, and used Table 2 for all new entries. Now I want to combine them all, hopefully keeping id and date in ascending order.

Upvotes: 5

Views: 13808

Answers (2)

Andy
Andy

Reputation: 46334

Figured it out.

  • Open current database.

  • Attach to original database 

    ATTACH '/orig/db/location' as orig

  • Move records from current database to old database, leaving out the PK

    insert into orig.restInterface_temp_entry(dateTime,sensor,temp)
...> select dateTime,sensor,temp from main.restInterface_temp_entry;

  • Clear current databases table

    delete from main.restInterface_temp_entry where id > 0

  • Copy everything updated records from original databases table back to current.

    insert into main.restInterface_temp_entry(id,dateTime,sensor,temp)
...> select id,dateTime,sensor,temp
...> from orig.restInterface_temp_entry;

Upvotes: 3

Gerald P. Wright
Gerald P. Wright

Reputation: 796

I'm assuming SQLLite supports INSERT INTO SELECT

INSERT INTO newtable (id, datetime, sensor, temp) 
    SELECT id, date, sensor, temp 
    FROM temp_entries
    ORDER BY id;
INSERT INTO newtable (id, datetime, sensor, temp) 
    SELECT "id", "dateTime", "sensor", "temp"
    FROM "restInterface_temp_entry"
    ORDER BY "id";

This should do the trick.

Upvotes: 0

Related Questions