在sqlite3中组合两个表 [英] Combining two tables in sqlite3

问题描述

我在两个独立的sqlite3数据库中有两个表。数据类型是相同的，但是模式略有不同。我希望他们在单个数据库中具有与相同的模式的单个表表2

表1

  CREATE TABLE temp_entries（
 id INTEGER PRIMARY KEY，
 sensor NUMERIC，
 temp NUMERIC， 
 date NUMERIC）; 
  

表2

  CREATE TABLErestInterface_temp_entry（
idinteger NOT NULL PRIMARY KEY，
dateTimeinteger NOT NULL，
sensorinteger NOT NULL，
 tempinteger NOT NULL 
）; 
  

id 。我想创建另一个表与表2 相同的模式。我想让表1中的条目的 id 从0开始，然后从表2 开始在表1 的最后一个条目之后。

理想情况下，我想添加表1 到表2 和reindex主键，使其与dateTime的升序相同。 / p>

UPDATE ：我现在有两个表使用相同的模式，我通过创建一个新表与相同的模式表2 插入保存的数据库表1 。我将数据复制到新表中，如：

  INSERT INTO restInterface_temp_entry（id，dateTime，sensor，temp）
 ...>选择id，date，sensor，temp FROM temp_entries; 
  

背景

一堆 temp_entries 到csv文件。我想把数据放到一个更容易工作的格式，并选择sqlite3。我写了一个程序，把所有的条目拉出来，并把它们放入表1 。我不确定我在做什么，并使用表2 所有新的条目。现在我想把它们结合起来，希望保持id和日期升序。

解决方案

看出来了。

• 打开当前数据库。
  



• 附加到原始数据库
  

    ATTACH'/ orig / db / location'as orig 
    




• 数据库到旧数据库，遗漏PK
  

   插入到orig.restInterface_temp_entry（dateTime，sensor，temp）
   ...>选择dateTime，sensor，temp from main.restInterface_temp_entry; 
    




• 清除当前数据库表
  

   从main.restInterface_temp_entry中删除，其中id> 0 
    




• 将所有更新的记录从原始数据库表复制回当前。 p>
  
  

   插入到main.restInterface_temp_entry（id，dateTime，sensor，temp）
   ...>选择id，dateTime，sensor，temp 
   ...>从orig.restInterface_temp_entry; 
    

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.

解决方案

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;
  

这篇关于在sqlite3中组合两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！