Re: insert into ... select ... and column order

Tore Halset wrote:
> Hello.
> 
> One of our users tried a "insert into ... select ..." that gave a 
> strange error message. After digging into the issue, the problem seem to 
> be that the order of the columns in the select statement must match the 
> table definition. Here is a way to reproduce this case.

> insert into dest_2 select USER_ID, PRODUCT_ID, min(PERMIT_START_DATE) as 
> PERMIT_START_DATE, max(PERMIT_END_DATE) as PERMIT_END_DATE from source 
> group by USER_ID, PRODUCT_ID;
> 
> Why does the column order matter when the subselect has all the correct 
> column names?

The names do not matter - the database won't try to match up the names. 
Think about it in comparison with INSERT ... VALUES - it's the same layout.

What you need to do is supply the column-names for the insert (this is a 
good idea anyway - it makes it explicit what is going on and will cope 
better if you change the definition of dest_2).

INSERT INTO dest_2 (permit_end_date, permit_start_date, ...)
SELECT , , ...

-- 
   Richard Huxton
   Archonet Ltd