CODEWITHSUNDEEP
sqloracle-databaseinsert
Matin
Matin

Reputation: 117

Multiple insert based on value of a field?

I have a table t1:

ID    Period
---  --------
1       5
2       3
3       2

(The table in fact has 366 different IDs and their period can be 23, 24, or 25.)

I want to insert multiple rows in a table t2 with fields ID and Hour, where Hour(ID) in 1 ..Period(ID):

ID    Hour
---  -----
1     1
1     2
1     3
1     4
1     5
2     1
2     2
2     3
3     1
3     2

How can I do it?

Upvotes: 2

Views: 157

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

The test setup:

CREATE TABLE t1 (
    ID INT PRIMARY KEY,
    Period INT NOT NULL
);

CREATE TABLE t2 (
    ID INT NOT NULL,
    Hour INT NOT NULL
    -- There would also be a PK here in real life.
);

INSERT INTO t1 VALUES (1, 5);
INSERT INTO t1 VALUES (2, 3);
INSERT INTO t1 VALUES (3, 2);

The actual insert:

INSERT INTO t2
WITH CTE (ID, Hour) AS (
    SELECT ID, Period FROM t1
    UNION ALL
    SELECT ID, Hour - 1 FROM CTE WHERE Hour > 1
)
SELECT * FROM CTE;

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

You can achieve this by using model clause, for example:

with t1(ID, Period) as(
  select 1, 5 from dual union all
  select 2, 3 from dual union all
  select 3, 2 from dual
)
select ID
     , period as hour
  from t1
  model
  partition by (ID)
  dimension by (1 as indx)
  measures(period)
  rules(
     period[for indx from 1 to period[1] increment 1] = cv(indx)
  )

SQLFiddle Demo

Result:

        ID      HOUR
---------- ----------
         1          1 
         1          2 
         1          3 
         1          4 
         1          5 
         2          1 
         2          2 
         2          3 
         3          1 
         3          2 

 10 rows selected

And your insert statement could look like this:

insert into t2(id, hour)
   select ID
        , period
     from t1
     model
     partition by (ID)
     dimension by (1 as indx)
     measures(period)
     rules(
        period[for indx from 1 to period[1] increment 1] = cv(indx)
     )

Upvotes: 4

Related Questions