10.4. Splitting Tables

Splitting tables involves moving some of the rows from one table to another table. Data is split for the purpose of separating some predetermined range of data, such as historical data from current data, so that query performance is increased. This reduces the burden imposed on queries that only access current data. The following PROC SQL example shows the current year’s data being copied and then removed from a table containing five years of data.

SQL Code

PROC SQL;
  CREATE TABLE INVENTORY_CURRENT AS
    SELECT *
      FROM INVENTORY
        WHERE YEAR(ORDDATE) = YEAR(TODAY());

  DELETE FROM INVENTORY
    WHERE YEAR(ORDDATE) = YEAR(TODAY());
QUIT;