When you use a SQL writeback connector — I'm specifically referring to Microsoft SQL Server, but others may be affected — it writes each column by position, rather than by name. This can cause problems:
- You've added a new field to your dataset, but it's migrated to somewhere in the middle of the schema (this is especially common when you use a Domo view). You alter the table to add the new field, but ALTER TABLE can only add columns to the end of the schema; it can't rearrange them into a new order. Unless you drop the table in the database and recreate it, every column from the new one to the end will be written to the wrong field.
- In the database, you've added a calculated column and an index to the table for performance. As long as you don't try to export to the calculated column the writeback works. However, you need to add two new columns using ALTER TABLE and they end up after the calculated column. When you add the new columns to the dataset and try the writeback, it tries to write the first one to the calculated column and the writeback fails. You can't easily drop the calculated column to move it because of the index, so the whole thing spirals.
I would like the Powers That Be to add the option to the connectors to let them write to column names instead of position. This will eliminate the problem of schema mismatch when columns are added or removed.