INSERT x WITH AUTO NAME SELECT * FROM y should not fail on "Column 'z' not found" when y.z exists but not x.z.
Sometimes it is necessary to create tables that contain a subset or superset of the columns from another table. An example of a subset might be a temporary table containing only primary keys. An example of a superset might be an audit table that has a different, additional column as primary key, plus other columns for auditing purposes.
It is also sometimes convenient to make use of INSERT WITH AUTO NAME together with SELECT * when copying from one table to another.
This works fine when the target is a superset, but not when it is a subset. IMO columns in the SELECT that do not exist in the target table should be ignored... after all, that's what WITH AUTO NAME is for, and it already ignores the inverse: columns in the target table which are not specified in the SELECT.
CREATE TABLE x ( a INTEGER, b INTEGER ); CREATE TABLE y ( a INTEGER, b INTEGER, z INTEGER ); INSERT y VALUES ( 1, 2, 3 ); COMMIT; -- This fails... INSERT x WITH AUTO NAME SELECT * FROM y; Could not execute statement. Column 'z' not found SQLCODE=-143, ODBC 3 State="42S22" Line 1, column 1 -- This works... INSERT x WITH AUTO NAME SELECT a, b FROM y; SELECT * FROM x; a,b 1,2 -- This also works... INSERT x WITH AUTO NAME SELECT b FROM y; SELECT * FROM x; a,b 1,2 (NULL),2
asked 19 Oct '10, 17:51
I would see this a different approach to the existing one. If I prepare a select for use with insert AUTO NAME if often code
And I like the check that all values included in the select can be inserted in the destination table.
could be a extension to the current behaviour an clearly indicate the different behaviour.
answered 20 Oct '10, 10:43
I'm not sure I would want this enhancement.
As you state, the opposite (inserting into a superset) does work, but it relies on the fact that column z is implicitly declared as NULLable and as such, a NULL value is inserted. This corresponds to the AUTO NAME feature description (from the SA 12 docs):
A simple sample based on your one:
So, here the "SELECT *" result set will be used and for non-contained columns, their default is used - similar to an INSERT with a specified column-list.
But in your suggestion, the result set of the "SELECT *" would only be used partially. IMHO (and it's more of a feeling), that would contradict my understanding of the INSERT SELECT construct - specifying lesser columns then are contained in the target seems naturally, specifying more columns not.
Just my 2 cents, and as said, somewhat "more of a feeling" than real reasoning:)
answered 20 Oct '10, 08:00