Importing a SQLite3 dump back into the database

Question

I feel like this is a stupid question because it seems like common sense . . . but no google search I can put together seems to be able to give me the answer!

I know how to get data OUT of a sqlite3 database using the .dump command. But now that I have this ASCII file titled export.sqlite3.sql . . . I can't seem to get it back INTO the database I want.

My goal was to transfer the data I had in one rails app to another so I didn't have to take all sorts of time creating dummy data again . . . so I dumped the data from my first app, got rid of all the CREATE TABLE statements, and made sure my schema on my second app matches . . . now I just have to get it IN there.

Would anyone mind helping me out? And when you find a way, will you tell me what you plugged into the google, 'cause I am beating my head open with a spoon right now over what I thought would be an easy find.

Answer 1

cat dumpfile.sql | sqlite3 my_database.sqlite

This is modified from the sqlite3 getting started guide.

Answer 2

You didn't specify your operating system and while

sqlite3 my_database.sqlite < export.sqlite3.sql

will work for the unix flavors, it will not work for windows.

The inverse of the .dump command is the .read command. The syntax would be

sqlite3> .read export.sqlite3.sql

Answer 3

This should also work:

echo '.read export.sqlite3.sql' | sqlite3 my_database.sqlite3

One possible advantage over "sqlite3 my_database.sqlite3 < export.sqlite3.sql" is that SQLite's .read command might (now or in the future) be more advanced than just "read in all the text and execute it." It might do batching, which would reduce memory usage for large dumps. I admit, though, that this is a pretty obscure and unlikely advantage. In all likelihood, .read simply reads each line from the input and executes it, just like the redirection and pipe operators.