Insert new column into table in sqlite?

I have a table with columns name, qty, rate. Now I need to add a new column COLNew in between the name and qty columns. How do I add a new column in between two columns?

You have two options. First, you could simply add a new column with the following:

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Second, and more complicatedly, but would actually put the column where you want it, would be to create the new table with the missing column and a temporary new name:

CREATE TABLE {tempNewTableName} (name TEXT, COLNew {type} DEFAULT {defaultValue}, qty INTEGER, rate REAL);

And populate it with the old data:

INSERT INTO {tempNewTableName} (name, qty, rate) SELECT name, qty, rate FROM OldTable;

Then delete the old table:

DROP TABLE OldTable;

Then rename the new table to have the name of the OldTable:

ALTER TABLE {tempNewTableName} RENAME TO OldTable;

I'd much prefer the second option, as it will allow you to completely rename everything if need be.

You don't add columns between other columns in SQL, you just add them. Where they're put is totally up to the DBMS. The right place to ensure that columns come out in the correct order is when you select them.

In other words, if you want them in the order {name,colnew,qty,rate}, you use:

select name, colnew, qty, rate from ...

With SQLite, you need to use alter table, an example being:

alter table mytable add column colnew char(50)

You can add new column with the query

ALTER TABLE TableName ADD COLUMN COLNew CHAR(25)

But it will be added at the end, not in between the existing columns.

SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table.

If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a" and "c" and that you want to insert column "b" from this table. The following steps illustrate how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,c);
INSERT INTO t1_backup SELECT a,c FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b, c);
INSERT INTO t1 SELECT a,c FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

Now you are ready to insert your new data like so:

UPDATE t1 SET b='blah' WHERE a='key'

ALTER TABLE {tableName} ADD COLUMN COLNew {type};
UPDATE {tableName} SET COLNew = {base on {type} pass value here};

This update is required to handle the null value, inputting a default value as you require. As in your case, you need to call the SELECT query and you will get the order of columns, as paxdiablo already said:

SELECT name, colnew, qty, rate FROM{tablename}

and in my opinion, your column name to get the value from the cursor:

private static final String ColNew="ColNew";
String val=cursor.getString(cursor.getColumnIndex(ColNew));

so if the index changes your application will not face any problems.

This is the safe way in the sense that otherwise, if you are using CREATE temptable or RENAME table or CREATE, there would be a high chance of data loss if not handled carefully, for example in the case where your transactions occur while the battery is running out.

I was facing the same problem and the second method proposed in the accepted answer, as noted in the comments, can be problematic when dealing with foreign keys.

My workaround is to export the database to a sql file making sure that the INSERT statements include column names. I do it using DB Browser for SQLite which has an handy feature for that. After that you just have to edit the create table statement and insert the new column where you want it and recreate the db.

In *nix like systems is just something along the lines of

cat db.sql | sqlite3 database.db

I don't know how feasible this is with very big databases, but it worked in my case.

I seldom add Answers to 11 year old questions. That said the answer with a lot of votes has a misleading line of code. I say misleading because I tried it and had no success. Here is the line of code I am referencing.

ALTER TABLE {tableName} RENAME TO TempOldTable

This is the line I tried in my first try at adding a Column into a DB Table that had already been created. It FAILED but WHY might be a better question. Any way here is the failing line of code.

   Dim tb As String = "IncomeTable"
   Dim sqlCmd As String = "$ALTER TABLE" '{tb}' "ADD COLUMN itNumVisit INTEGER"

So here is the final code that adds a new Column in my case an INTEGER type.

    Private Sub btnCopyTable_Click(sender As Object, e As EventArgs) Handles btnCopyTable.Click

    Dim sqlCmd As String = "ALTER TABLE IncomeTable ADD COLUMN itNumVisit INTEGER"
    Try
        Using conn As New SQLiteConnection($"Data Source = '{gv_dbName}';Version=3;")
            conn.Open()

            Using cmd As New SQLiteCommand(sqlCmd, conn)

                cmd.ExecuteNonQuery()
            End Using
        End Using

    Catch ex As Exception
        MsgBox("It Failed")
    End Try
End Sub

Notice the STRING sqlCmd is all one String. Just in case someone tried the accepted Answer!