Inserting into a Columnstore table from a view used to work in 1.2 and below. A customer noticed that in 5.x and above that it no longer functions. See this example:
CREATE DATABASE monty;
|
USE monty;
|
CREATE TABLE foo (id INT, fname VARCHAR(20));
|
INSERT INTO foo VALUES (1,'Greg');
|
CREATE VIEW view_foo as SELECT * FROM foo;
|
SELECT * FROM view_foo;
|
CREATE TABLE bar (id INT, fname VARCHAR(20)) ENGINE=Columnstore;
|
INSERT INTO bar SELECT * FROM view_foo;
|
– ERROR: The storage engine for the table doesn't support IDB-1011: Insert on VIEW is currently not supported.
However, if we use the wrapper trick, it does work:
INSERT INTO bar SELECT * FROM (SELECT * FROM view_foo) t;
|
– OK: 1 row affected