I have a BTEQ script which was running fine, it has a MERGE stmt. the table in which data was MERGED to has got a new column added and the database default is defined for the new column. So my understanding was that anywhere this table is used in the script (for data INSERTion/MERGE) it will automatically pass the default value defined at the database level. but it failed with error below
**** Failure 3812. the positional assignment list has too few values
will the MERGE stmt not pick the default at database level?
If you do a positional INSERT tableName VALUES (...) into a table with N columns, the list must have exactly N entries (and N-1 commas). You can omit a value (leading, trailing, or two consecutive commas) if you want the default inserted but you must account for all the columns defined in the table. So when you add a column, you must at least add a trailing comma to the value list.
But if you use an explicit list of columns like INSERT tableName (col1, col4, col3) VALUES (...) then you just need the same number of entries in the column list before the VALUES keyword and the value list after. Columns with default values need not be mentioned unless you want to assign something other than the default value.