将数据从一个SQLite数据库复制到另一个

I have 2 SQLite databases with common data but with different purposes and I wanted to avoid reinserting data, so I was wondering if it was possible to copy a whole table from one database to another?

我有两个SQLite数据库，它们有通用的数据，但是用途不同，我想避免重新插入数据，所以我想知道是否可以将整个表从一个数据库复制到另一个数据库?

6 个解决方案

#1

You'll have to attach Database X with Database Y using the ATTACH command, then run the appropriate Insert Into commands for the tables you want to transfer.

您必须使用attach命令将数据库X与数据库Y连接起来，然后将适当的插入运行到要传输的表的命令中。

INSERT INTO X.TABLE SELECT * FROM Y.TABLE;

Or, if the columns are not matched up in order:

或者，如果列没有按顺序匹配:

INSERT INTO X.TABLE(fieldname1, fieldname2) SELECT fieldname1, fieldname2 FROM Y.TABLE;

#2

Consider a example where I have two databases namely allmsa.db and atlanta.db. Say the database allmsa.db has tables for all msas in US and database atlanta.db is empty.

考虑一个例子，我有两个数据库，即allmsa。db和atlanta.db。allmsa说数据库。db拥有所有msas在美国和数据库亚特兰大的表。db是空的。

Our target is to copy the table atlanta from allmsa.db to atlanta.db.

我们的目标是从allmsa复制亚特兰大的表格。db atlanta.db。

Steps

1. sqlite3 atlanta.db(to go into atlanta database)
2. sqlite3亚特兰大。db(进入亚特兰大数据库)
3. Attach allmsa.db. This can be done using the command ATTACH '/mnt/fastaccessDS/core/csv/allmsa.db' AS AM; note that we give the entire path of the database to be attached.
4. 附上allmsa.db。这可以使用命令ATTACH '/mnt/fastaccessDS/core/csv/allmsa来完成。db的我;注意，我们给出了要附加的数据库的整个路径。
5. check the database list using sqlite> .databases you can see the output as
6. 使用sqlite> .database检查数据库列表，您可以看到输出为

seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /mnt/fastaccessDS/core/csv/atlanta.db                  
2    AM               /mnt/fastaccessDS/core/csv/allmsa.db 

4. now you come to your actual target. Use the command INSERT INTO atlanta SELECT * FROM AM.atlanta;
5. 现在你到了真正的目标。使用插入到atlanta .atlanta . SELECT *的命令;

This should serve your purpose.

这应该符合你的目的。

#3

Easiest and correct way on a single line:

简单而正确的方法:

sqlite3 old.db ".dump mytable" | sqlite3 new.db

The primary key and the columns types will be kept.

将保留主键和列类型。

#4

For one time action, you can use .dump and .read.

对于一次操作，您可以使用.dump和.read。

Dump the table my_table from old_db.sqlite

从old_db.sqlite转储表my_table。

c:\sqlite>sqlite3.exe old_db.sqlite
sqlite> .output mytable_dump.sql
sqlite> .dump my_table
sqlite> .quit

Read the dump into the new_db.sqlite assuming the table there does not exist

将转储读入new_db。假设该表不存在的sqlite

c:\sqlite>sqlite3.exe new_db.sqlite
sqlite> .read mytable_dump.sql

Now you have cloned your table. To do this for whole database, simply leave out the table name in the .dump command.

现在您已经克隆了您的表。要对整个数据库执行此操作，只需在.dump命令中删除表名。

Bonus: The databases can have different encodings.

额外的好处:数据库可以有不同的编码。

#5

Objective-C code for copy Table from a Database to another Database

用于将表从数据库复制到另一个数据库的Objective-C代码

-(void) createCopyDatabase{

          NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
          NSString *documentsDir = [paths objectAtIndex:0];

          NSString *maindbPath = [documentsDir stringByAppendingPathComponent:@"User.sqlite"];;

          NSString *newdbPath = [documentsDir stringByAppendingPathComponent:@"User_copy.sqlite"];
          NSFileManager *fileManager = [NSFileManager defaultManager];
          char *error;

         if ([fileManager fileExistsAtPath:newdbPath]) {
             [fileManager removeItemAtPath:newdbPath error:nil];
         }
         sqlite3 *database;
         //open database
        if (sqlite3_open([newdbPath UTF8String], &database)!=SQLITE_OK) {
            NSLog(@"Error to open database");
        }

        NSString *attachQuery = [NSString stringWithFormat:@"ATTACH DATABASE \"%@\" AS aDB",maindbPath];

       sqlite3_exec(database, [attachQuery UTF8String], NULL, NULL, &error);
       if (error) {
           NSLog(@"Error to Attach = %s",error);
       }

       //Query for copy Table
       NSString *sqlString = @"CREATE TABLE Info AS SELECT * FROM aDB.Info";
       sqlite3_exec(database, [sqlString UTF8String], NULL, NULL, &error);
        if (error) {
            NSLog(@"Error to copy database = %s",error);
        }

        //Query for copy Table with Where Clause

        sqlString = @"CREATE TABLE comments AS SELECT * FROM aDB.comments Where user_name = 'XYZ'";
        sqlite3_exec(database, [sqlString UTF8String], NULL, NULL, &error);
        if (error) {
            NSLog(@"Error to copy database = %s",error);
        }
 }

#6

I needed to move data from a sql server compact database to sqlite, so using sql server 2008 you can right click on the table and select 'Script Table To' and then 'Data to Inserts'. Copy the insert statements remove the 'GO' statements and it executed successfully when applied to the sqlite database using the 'DB Browser for Sqlite' app.

我需要将数据从sql server compact数据库移动到sqlite，因此使用sql server 2008，您可以右键单击该表，选择“Script table to”，然后选择“data to insert”。复制插入语句，删除“GO”语句，并在使用“用于sqlite的DB浏览器”应用于sqlite数据库时成功执行。