Sqlite Insert into Table Select * From

sqlite insert into table select * from

explicitly specify the column name in the INSERT clause,

INSERT INTO destinationTable (risposta, data_ins)
SELECT STATUS risposta, DATETIME('now') data_ins 
FROM   sourceTable

SQLite INSERT with SELECT

Put your select in Brackets:

INSERT INTO Players 
VALUES('Name', 
       10.0, 
       (SELECT COUNT(*) AS Amount FROM Stack7 WHERE Name LIKE '%Name%'), 
       1.0);

In this way the compiler knows where the one value defined by your select statement starts and where it ends. And you are able to use a comma (,) inside your brackets if needed.

SQLite: How to retrieve data from column in one table using SELECT to insert retrieved data in another table

I suspect that what you want is this:

INSERT INTO "mutants.teams" (team, members)
SELECT team, COUNT(*) 
FROM "mutants.info"
GROUP BY team

This query selects all the (distinct) team names from "mutants.info" and inserts them in "mutants.teams" with the number of times each team appears in "mutants.info".

I don't know why you use in your code INSERT OR REPLACE instead of just INSERT.

If there are already rows in "mutants.teams" and you want them replaced by the new rows if there is a unique constraint violation on the team's name then fine.

Sqlite - SELECT or INSERT and SELECT in one statement

Your command does not work because in SQL, INSERT does not return a value.

If you have a unique constraint/index on the data column, you can use that to prevent duplicates if you blindly insert the value; this uses SQLite's INSERT OR IGNORE extension:

INSERT OR IGNORE INTO "Values"(data) VALUES('SOME_DATE');
SELECT id FROM "Values" WHERE data = 'SOME_DATA';

Using insert into database.table1 select * from table2 where id=some_value;

In the end I made an in-memory database, with the same table except leaving off "primary key". I did a first "insert into mytable select * from ... " into the in-memory database, then set absid to null, and then a second "insert into ..." into the target db. That way I got the row copied and with a new key value.

Although there is a second step, this whole action (in my app) is not done with huge numbers of rows so efficiency becomes secondary to maintainability.

SQLite Insert value and select

Here is the syntax you are looking for:

INSERT INTO table1 (id, name)
SELECT 1, otherName
FROM table2

Have a look at this SO article which covers a similar question.

---

Related Topics