Copy and paste the script below into the SQL console. Press the Execute button to execute the migration. Ensure the entire script executed correctly. The script below copies records older than 2015-1-1 from the in-memory LINEITEM_CS
table to the extended table LINEITEM_DT
in Dynamic Tiering. It then deletes the moved records from the LINEITEM_CS
table to free up storage space in-memory now that the data has been copied to Dynamic Tiering. The “WHERE
” statement in the script is used to selectively choose data. Specifically, in the script above, the “WHERE
” statement is used to select data from the LINEITEM_CS
table whose “L_SHIPDATE
” is before January 1st, 2015.
INSERT INTO "TPCH"."LINEITEM_DT"
(SELECT * FROM "TPCH"."LINEITEM_CS"
WHERE "TPCH"."LINEITEM_CS"."L_SHIPDATE" < '2015-1-1');
DELETE FROM "TPCH"."LINEITEM_CS"
WHERE "TPCH"."LINEITEM_CS"."L_SHIPDATE" < '2015-1-1';
COMMIT;
Because you have turned off Auto Commit, you now need to explicitly commit the transaction yourself using the “COMMIT” statement.
Table LINEITEM_CS
has records with ship dates ranging from Jan 2, 2012 (2012-1-2) to Dec 1, 2018 (2018-12-1). For the purpose of this lesson, you will be migrating records older than Jan 1, 2015 (2015-1-1) from the ORDERS_CS
table to the ORDERS_DT
table.
Verify that the data has been inserted into the LINEITEM_DT
table either by executing the query below or by right clicking on the table in the catalogue and choosing Open Data Preview.
SELECT * FROM "TPCH"."LINEITEM_DT";
Verify that the data has been deleted from the LINEITEM_CS
table by executing the query below and confirming that no records are returned.
SELECT * FROM "TPCH"."LINEITEM_CS" WHERE "TPCH"."LINEITEM_CS"."L_SHIPDATE" < '2015-1-1';