Multi table insert - a forgotten gem



We've had cause to create test data today where we wanted to duplicate the data already existing in the table 3 times but for each copy change the 'business date'. So the end result show be 4 complete copies of the data each with a different business date but in every other way the same. This could be done in a number of ways but the most efficient way would seem to be to make use of multitable insert which means the souce only has to be read once in order to create the 3 separate copies.

In the example below we insert into user_eet_risk_vega 3 times by selecting from user_eet_risk_vega.

insert all into user_eet_risk_vega
values
  (BUSINESS_DATE - 1,
   DEAL_NUM,
   TRAN_NUM,
   INDEX_NAME,
   RISK_MONTH,
   EBIT_MONTH,
   VEGA,
   CREATED_DATE) into user_eet_risk_vega
values
  (BUSINESS_DATE - 2,
   DEAL_NUM,
   TRAN_NUM,
   INDEX_NAME,
   RISK_MONTH,
   EBIT_MONTH,
   VEGA,
   CREATED_DATE) into user_eet_risk_vega
values
  (BUSINESS_DATE - 3,
   DEAL_NUM,
   TRAN_NUM,
   INDEX_NAME,
   RISK_MONTH,
   EBIT_MONTH,
   VEGA,
   CREATED_DATE)
  SELECT BUSINESS_DATE,
         DEAL_NUM,
         TRAN_NUM,
         INDEX_NAME,
         RISK_MONTH,
         EBIT_MONTH,
         VEGA,
         CREATED_DATE
    from user_eet_risk_vega;


In the example below the original table had 10 million rows, we add 30 million by running the SQL above and we then have 4 distinct sets of data for each business_date.

multitable insert is much more powerful tha shown above but even in the simple case it can provide great efficiencies.

Comments