I've been burned by this several times: INSERT...SELECT, and inadvertantly leaving one or more columns off my SELECT STATEMENT:
create table temp1 (c1 varchar(1), c2 varchar(1));
insert into temp1 select 'x';
The above command runs without error, and populates column C2 with null. I'd much prefer to have the statement raise an error. I suppose there's a "shame on me" aspect to this; perhaps I should be in the habit of coding all INSERTs as follows:
create table kk2796_temp1 (c1 varchar(1), c2 varchar(1));
insert into kk2796_temp1(c1, c2) select 'x';
I can only speak to my own line of thinking here, but this is how I typically reason about INSERT syntax:
If I am inserting records into a subset of columns of a table, then I should explicitly indicate which columns should be affected. However, if I am inserting records into all columns of a table, then I can use a shorthand (omitting column specifications on INSERT). In other words, when I code using the form of the initial listing, my expectation is that the database engine will implicitly convert it to the second listing.
Clearly, this isn't the case (the first INSERT gives no error; the second one fails). Is there a setting I can use to force the two commands to behave identically? Or any way to get the sanity check w/out listing out all columns of a table I'm inserting into (which are sometimes hundred of columns)? I'd even be okay with an alternative syntax, e.g.:
insert into temp1 (*) select 'x';
. 
