9

I need a simple way to export data from an SQLite database of multiple tables, then import them into another database.

Here is my scenario. I have 5 tables: A, B, C, D, E.

Each table has a primary key as the first column called ID. I want a Unix command that will dump ONLY the data in the row from the primary key in a format that can be imported into another database.

I know I can do a 

sqlite3 db .dump | grep INSERT

but that gives me ALL data in the table. I'm not a database expert, and I'm trying to do this with all unix commands in which I can write a shell script, rather than writing C++ code to do it (because that's what people are telling me that's the easiest way). I just refuse to write C++ code to accomplish a task that possible can be done in 4-5 statements from the command line.

Any suggestions?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
David Vergolini
  • 91
  • 1
  • 1
  • 2
  • Just noted.....I did a dump of all the data and tried to import into the same database. The primary key needs to be unique. So my script would need to create unique keys? Or if I strip off the primary key and it is set to auto-increment, will an insert operation generate a unique key? – David Vergolini Dec 08 '10 at 17:08
  • In addition, the table structure where the data will be imported will be the same, and I have to export and import all the data from the 5 tables that are associated with the unique ID. So if all 5 tables have a unique ID of 1 with columns of data, I need to export that into a file, then import that into the same table structure on another system (the database files will exist). – David Vergolini Dec 08 '10 at 17:16

4 Answers4

11

This may look a little weird, however you may give it a try:

Create text file and place following statements there:

.mode insert
.output insert.sql
select * from TABLE where STATEMENT; -- place the needed select query here
.output stdout

Feed this file to sqlite3:

$ sqlite3 -init initf DATA.DB .schema > schema.sql

As the result you will get two files: one with simple "inserts" (insert.sql) and another with db schema (schema.sql).

barti_ddu
  • 10,179
  • 1
  • 45
  • 53
  • 5
    I'd suggest including the table name when changing the mode: `.mode insert TABLE`. This will make it include the table's name in the generated insert statements. – Brigham Feb 09 '14 at 04:14
2

Suggest finding a tool that can take your query and export to a CSV. It sounds like you wanted a script. Did you want to reuse and automate this?

For other scenarios, perhaps consider the sqlite-manager Firefox plugin. It supports running your adhoc queries, and exporting the results to a CSV.

Within that, give it this statement:

 SELECT ID FROM TableA

Repeat for each table as you need.

p.campbell
  • 98,673
  • 67
  • 256
  • 322
  • sqlite3 will dump into CSV....this is not my problem,and I can't use a 3rd party tool. It is a closed system, intended for unique users (not PCs). The system will use Unix as the OS, hence my attempt to find unix command line solutions. My essential goal is to export based on primary key into a file format, then take that file to another similar system and import the data into that database. – David Vergolini Dec 08 '10 at 17:10
2

You can use sqlite3 bash. For example if you want to get insert query for all records in one table, you can do the followings:

$ sqlite3 /path/to/db_name.db
>>.mode insert
>>.output insert.sql
>>select * from table_name;

It will creates a file name called insert.sql and puts insert query for every record in the given table.

A sample for what you get in insert.sql:

INSERT INTO "table" VALUES("data for record one");
INSERT INTO "table" VALUES("data for record two");
..
Ghasem
  • 14,455
  • 21
  • 138
  • 171
0

you also can use the quote function of SQLite.

echo "SELECT 'INSERT INTO my_new_table (my_new_key) VALUES (' || quote(my_old_key) || ');' FROM my_old_table;" | sqlite my_table > statements.sql
Benoit
  • 76,634
  • 23
  • 210
  • 236