database 将数据从一个 SQLite 数据库复制到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2359205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying data from one SQLite database to another
提问by Leonardo Marques
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?
我有 2 个具有通用数据但用途不同的 SQLite 数据库，我想避免重新插入数据，所以我想知道是否可以将整个表从一个数据库复制到另一个数据库？
回答by Michael D. Irizarry
You'll have to attach Database X with Database Y using the ATTACHcommand, then run the appropriate Insert Into commands for the tables you want to transfer.
您必须使用ATTACH命令将数据库 X 与数据库 Y 连接，然后为要传输的表运行适当的 Insert Into 命令。
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;
回答by Neeraj Chandak
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 的表，而数据库 atlanta.db 是空的。
Our target is to copy the table atlanta from allmsa.db to atlanta.db.
我们的目标是将 atlanta 表从 allmsa.db 复制到 atlanta.db。
Steps
脚步
- sqlite3 atlanta.db(to go into atlanta database)
- 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. - check the database list using
sqlite> .databasesyou can see the output as
- sqlite3 atlanta.db（进入亚特兰大数据库）
- 附上 allmsa.db。这可以使用
ATTACH '/mnt/fastaccessDS/core/csv/allmsa.db' AS AM;我们给出要附加的数据库的完整路径的命令注释来完成。 - 使用检查数据库列表，
sqlite> .databases您可以看到输出为
seq name file --- --------------- ---------------------------------------------------------- 0 main /mnt/fastaccessDS/core/csv/atlanta.db 2 AM /mnt/fastaccessDS/core/csv/allmsa.db
- now you come to your actual target. Use the command
INSERT INTO atlanta SELECT * FROM AM.atlanta;
- 现在你来到了你的实际目标。使用命令
INSERT INTO atlanta SELECT * FROM AM.atlanta;
This should serve your purpose.
这应该符合您的目的。
回答by Bernardo Ramos
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.
将保留主键和列类型。
回答by Thinkeye
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.
奖励：数据库可以有不同的编码。
回答by avinash
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);
}
}
回答by Michael Gabay
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 紧凑型数据库移动到 sqlite，因此使用 sql server 2008，您可以右键单击该表并选择“脚本表到”，然后选择“要插入的数据”。复制插入语句，删除 'GO' 语句，并在使用 'DB Browser for Sqlite' 应用程序应用于 sqlite 数据库时成功执行。
回答by Able Mac
First scenario: DB1.sqlite and DB2.sqlite have the same table(t1), but DB1 is more "up to date" than DB2. If it's small, drop the table from DB2 and recreate it with the data:
第一种情况：DB1.sqlite 和 DB2.sqlite 具有相同的表 (t1)，但 DB1 比 DB2 更“最新”。如果它很小，请从 DB2 中删除该表并使用数据重新创建它：
> DROP TABLE IF EXISTS db2.t1; CREATE TABLE db2.t1 AS SELECT * FROM db1.t1;
Second scenario: If it's a large table, you may be better off with an INSERT if not existstype solution. If you have a Unique Keycolumn it's more straight forward, otherwise you'd need to use a combination of fields (maybe every field) and at some point it's still faster to just dropand re-createthe table; it's always more straight forward (less thinking required).
第二种情况：如果它是一张大表，那么使用INSERT if not exists类型解决方案可能会更好。如果你有一个Unique Key列，它会更直接，否则你需要使用字段的组合（可能是每个字段），并且在某些时候它仍然更快地drop重新创建create表格；它总是更直接（需要更少的思考）。
THE SETUP: open SQLite without a DB which creates a temporaryin memory maindatabase, then attachDB1.sqlite and DB2.sqlite
设置：打开没有数据库的 SQLite，它会创建一个temporary内存main数据库，然后是attachDB1.sqlite 和 DB2.sqlite
> sqlite3
sqlite> ATTACH "DB1.sqlite" AS db1
sqlite> ATTACH "DB2.sqlite" AS db2
and use .databasesto see the attached databases and their files.
并用于.databases查看附加的数据库及其文件。
sqlite> .databases
main:
db1: /db/DB1.sqlite
db2: /db/DB2.sqlite
