It is not possible to merge data (insert else update) into a columnstore table with one command, correct? The documentation for INSERT … ON DUPLICATE KEY UPDATE and REPLACE indicate that they can merge when a primary or unique key is defined. However, columnstore tables do not support primary keys and merging does not seem to work with an unenforced unique key. As such, it seems that separate commands would be required to insert where key not exists, update for key where values differ, and delete where key exists.
That is correct, there is no command for merge like INSERT … ON DUPLICATE KEY UPDATE or REPLACE functionality for columnstore tables.
1 Like
What is the best practice in working around this? Hold the new records in a staging table then you can delete, insert from staging, and then delete from staging?
Yes, that is the most common approach.