On 25 Aug 2015, at 10:27pm, Tim Streater <tim at clothears.org.uk> wrote:
> So how does that work with:
>
> create table newtable as select * from oldtable;
>
> Does that mean that, in principle, I could have any random set of column
> names for my new table?
Future versions of SQLite can do anything. In real life, for that specific
command, for column names which are just letters and digits, you'll get what
you expect. However ...
If you have column names surrounded by double-quotes or square brackets, do you
expect them to be part of the column names ? The SQL specification is a little
weird on the issue.
Also, suppose you did
create table newtable as select * from myView
and one of the view columns was a calculation. What do you expect the name of
the corresponding column to be ?
> Doing this properly is also going to mean that, for safety, I've got 306
> select statements to fix up in my application. It's gonna be a bit dull if I
> have one where I select 20 named columns from a table and have to change such
> as:
>
> select absid, firstname, lastname, phone, ...
>
> to:
>
> select absid as absid, firstname as firstname, lastname as lastname, phone
> as phone, ...
Alternatively you could use the columns which are in specific places. For
example, if lastname is the third column of the SELECT command, its values will
always be the third column of the response. Not only will this always work but
it will be faster since it's faster to process the index 3 than to look up a
string.
Simon.
