import command, then be sure to follow the advice in the sqlite FAQ for INSERT speed: If you do put a loop around an INSERT, rather than using the CLI. separator STRING Change separator used by output mode and. import FILE TABLE Import data from FILE into TABLE Sqlite3 can't do that directly in SQL except via a SELECT, and while SELECT can return a "row" of expressions, I know of no way to make it return a phony column. P.S.: Please +1 river's reply, as it presented the solution first. We're using UNION ALL since it more closely matches the semantics of the original post. If you omit the ALL, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). a note on UNION vs UNION ALLĪs several people commented, if you use UNION ALL (as shown above), all rows will be inserted, so in this case, you'd get four rows of data1, data2. If efficiency is your goal, you should try this first. INSERT INTO 'tablename' table VALUES ('data3', 'data4') INSERT INTO 'tablename' table VALUES ('data1', 'data2') However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTs within a single transaction: BEGIN TRANSACTION I originally used this technique to efficiently load large datasets from Ruby on Rails. SELECT 'data1' AS 'column1', 'data2' AS 'column2' This can be recast into SQLite as: INSERT INTO 'tablename' To make it perfectly clear, the OPs MySQL example: INSERT INTO 'tablename' ('column1', 'column2') VALUES If I had privileges, I would bump river's reply: You can insert multiple rows in SQLite, you just need different syntax. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases. As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |