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.

Solution 1

cat dumpfile.sql | sqlite3 my_database.sqlite

This is modified from the sqlite3 getting started guide.

Solution 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

Solution 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.

Comments

[converter42]

Excellent answer. At the risk of sounding like a broken record, this is useless use of cat. Redirection is the answer: sqlite3 my_database.sqlite < dumpfile.sql You can also perform the entire operation in a pipeline: sqlite3 old_database.sqlite .dump | sqlite3 my_database.sqlite

[Ali Afshar]

I agree, I usually use it in the < form too. Just trying to be explicit. I actually modified the example in the documentation which is using zcat.

[Java Man]

unix flavors command not working.. did you restore using this?

[Java Man]

Error: cannot open "export./home/ubuntu/trydatabase/newsql.sql" using this command

[Noah]

yes, but be aware that the sqlite3> is not a unix command, it is the sqlite3 prompt

[Java Man]

i got the solution without export statement it restores..Thanks anyway

[Brian]

I recommend prepending "PRAGMA journal_mode = OFF; PRAGMA synchronous = OFF;" to the dump file before running this. This makes restores much faster. Obviously these settings should not be OFF when actually using the restored database.

[mwfearnley]

What does 'Ctrl+alt+T' do on all Unix flavours?

[M. Mimpen]

It opens the terminal.

[mdisibio]

echo .dump | sqlite3 sourcedb.sqlite | sqlite3 targetdb.sqlite seems to work on windows...

[Paul]

60 MB, 220'000 rows, in 10 seconds with prepending "PRAGMA journal_mode = OFF; PRAGMA synchronous = OFF;" thanks @Brian

[JohnyTex]

Why do I need to import a dump of the database? Can't I just use the database.db file to begin with?