sqlite alter table add MULTIPLE columns in a single statement

SqlSqliteAlter Table

Sql Problem Overview


Is it possible to alter table add MULTIPLE columns in a single statement in sqlite? The following would not work.

alter table test add column mycolumn1 text, add column mycolumn2 text;

Sql Solutions


Solution 1 - Sql

No, you have to add them one at a time. See the syntax diagram at the top of SQLite's ALTER TABLE documentation:

ALTER TABLE syntax

There's no loop in the ADD branch so no repetition is allowed.

Solution 2 - Sql

The only thing so far possible that I use is

BEGIN TRANSACTION;
ALTER TABLE tblName ADD ColumnNameA TEXT DEFAULT '';
ALTER TABLE tblName ADD ColumnNameB TEXT DEFAULT '';
ALTER TABLE tblName ADD ColumnNameC TEXT DEFAULT '';
COMMIT

Note that there are ; on purpose to make the query be read as multiple lines.

Then I run this query and get multiple columns added in on run... So no not in one line, but yes in one query its possible.

Solution 3 - Sql

The answer from @mu is too short' is right. As an extra, adding an optimized workaround for adding multiple columns using the benefit of transactions in SQL.

String alterTableQuery = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN ";
List<String> newColumns = ..// Your new columns

db.beginTransaction();
for (String column : newColumns){
    db.execSQL(alterTableQuery + column +  " VARCHAR");
}
db.setTransactionSuccessful();
db.endTransaction();

I hope this will help someone.

Solution 4 - Sql

alter table test add column mycolumn1 text; alter table test add column mycolumn2 text;

use the above redifined query

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionuser775187View Question on Stackoverflow
Solution 1 - Sqlmu is too shortView Answer on Stackoverflow
Solution 2 - SqlMichiel KrolView Answer on Stackoverflow
Solution 3 - SqlArun P MView Answer on Stackoverflow
Solution 4 - Sqlanand arunView Answer on Stackoverflow