how to ALTER in-Memory DB table and add new columns?
i have a large model that i don't want to wipe out and rebuild.
- i was hoping to learn syntax for sql script to "alter table [tableName] Add [tableName].[newColumn] char(8)" type of command.
- i would also be willing to create a new model with that in-mem db as both source and target. my attempt to use column-operations and inject a Calculated column (even if empty string) was unsuccessful.
any guidance or supporting documention would be greatly appreciated
1 reply
-
Hi ,
IMDB is a compressed, column-store in-memory relational database. The important bit here is the "compressed, column-store". This means the data has been compressed and the compression based on the table columns and their variation in value, i.e. column containing gender values will be highly compressed, whereas a unique transaction id, not at all.
For this reason it is not possible to directly add a column to an existing table with data unless it is derived from existing columns. To add a brand new column would require the whole table to be decompressed, the column added then the whole table recompressed. It's easier and quicker to just drop the table and recreate / reload it. There are options in the Model data flow to "synchronise" tables during processing, i.e. if any new columns are added to the source DB, add them to the table, or if any are removed, remove them from the model.
If you were working with a standard SQL DB, then you can use Master Flows and its SQL block to process SQL scripts against those databases to do anything you want.
It's outside of our scope to teach SQL syntax, but there are plenty of online resources to help you there.
Hope that helps,
Ian