Run multiple commands in sqlite manager
Solution is very simple ;-)
-> use a semicolon to separate the commands
insert into TestTable (Name, Age) values("Thomas", 25);
insert into TestTable (Name, Age) values("Peter", 29);
...
PDO+SQLite does not run multiple statements in one query?
The documentation says:
PDO::query() executes an SQL statement
And it did indeed execute an SQL statement.
PDO is not the CLI.
If you want to execute multiple statements, use multiple query
calls.
Suggested way to run multiple sql statements in python?
I would create a stored procedure:
DROP PROCEDURE IF EXISTS CopyTable;
DELIMITER $$
CREATE PROCEDURE CopyTable(IN _mytable VARCHAR(64), _table_name VARCHAR(64))
BEGIN
SET FOREIGN_KEY_CHECKS=0;
SET @stmt = CONCAT('DROP TABLE IF EXISTS ',_table_name);
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
SET FOREIGN_KEY_CHECKS=1;
SET @stmt = CONCAT('CREATE TABLE ',_table_name,' as select * from ', _mytable);
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
and then just run:
args = ['mytable', 'table_name']
cursor.callproc('CopyTable', args)
keeping it simple and modular. Of course you should do some kind of error checking and you could even have the stored procedure return a code to indicate success or failure.
How to delete multiple rows in Sqlite Manager
Finally i've found my solution, its just simple condition code:
DELETE FROM `TABLE_NAME`
WHERE `id` > '100'
after you run this code, all rows which have id > 100 will be deleted.
you can change the condition as you want, like changing table name, condition name (in my case its "id"), and the condition "<" or ">"...
i hope its helpfull..!
multiple sql statements in QSqlQuery using the sqlite3 driver
As clearly stated in Qt Documentation for QSqlQuery::prepare() and QSqlQuery::exec(),
For SQLite, the query string can contain only one statement at a time.
If more than one statements are give, the function returns false.
As you have already guessed the only known workaround to this limitation is having all the sql statements separated by some string, split the statements and execute each of them in a loop.
See the following example code (which uses ";" as separator, and assumes the same character not being used inside the queries..this lacks generality, as you may have the given character in string literals in where/insert/update statements):
QSqlDatabase database;
QSqlQuery query(database);
QFile scriptFile("/path/to/your/script.sql");
if (scriptFile.open(QIODevice::ReadOnly))
{
// The SQLite driver executes only a single (the first) query in the QSqlQuery
// if the script contains more queries, it needs to be splitted.
QStringList scriptQueries = QTextStream(&scriptFile).readAll().split(';');
foreach (QString queryTxt, scriptQueries)
{
if (queryTxt.trimmed().isEmpty()) {
continue;
}
if (!query.exec(queryTxt))
{
qFatal(QString("One of the query failed to execute."
" Error detail: " + query.lastError().text()).toLocal8Bit());
}
query.finish();
}
}
Is it possible to insert multiple rows at a time in an SQLite database?
update
As BrianCampbell points out here, SQLite 3.7.11 and above now supports the simpler syntax of the original post. However, the approach shown is still appropriate if you want maximum compatibility across legacy databases.
original answer
If I had privileges, I would bump river's reply: You can insert multiple rows in SQLite, you just need different syntax. To make it perfectly clear, the OPs MySQL example:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
This can be recast into SQLite as:
INSERT INTO 'tablename'
SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
UNION ALL SELECT 'data1', 'data2'
a note on performance
I originally used this technique to efficiently load large datasets from Ruby on Rails. However, as Jaime Cook points out, it's not clear this is any faster wrapping individual INSERTs
within a single transaction:
BEGIN TRANSACTION;
INSERT INTO 'tablename' table VALUES ('data1', 'data2');
INSERT INTO 'tablename' table VALUES ('data3', 'data4');
...
COMMIT;
If efficiency is your goal, you should try this first.
a note on UNION vs UNION ALL
As 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 you omit the ALL
, then duplicate rows will be eliminated (and the operation will presumably be a bit slower). We're using UNION ALL since it more closely matches the semantics of the original post.
in closing
P.S.: Please +1 river's reply, as it presented the solution first.
Related Topics
SQL Server:Find Duplicates in a Table Based on Values in a Single Column
Oracle (11.2.0.1):How to Identify the Row Which Is Currently Updated by the Update Statement
SQL Server Normalization Tactic: Varchar VS Int Identity
Modify(Replace) Xml for Conditions
How to Find Specific Value to Specific String Location in Different Strings
Fetch Table Name from a Column for from Clause
Using Tuples to Put More Than 1000 Entries in SQL in Clause
How to Do Forward Fill as a Pl/Pgsql Function
Months Between Two Dates in SQL Server with Starting and End Date of Each of Them in SQL Server
Using Oracle Combine Three Tables to One with Pivot
Can Vba in Ms Access Using Parameter to Prevent SQL Injection
Determine Contiguous Dates in SQL Gaps and Islands
Linq to SQL "Not Like" Operator