Insert new column into table in sqlite?

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)