Select from one table and insert into another table. 1

[hello99hello]

Hello All, I am trying to write sql to achive the following.

I have a table1 with the following columns:
Employee ID
Name
Plan
Type

Sample of records in the table are the following:
2067,Janice,LIFE,Y -------record 1
2067,Janice,MSP,Y -------record 2
2045,Enry,LIFE,Y -------record 3
2045,Enry,OPT,N -------record 4
2345, Louis,MSP,N -------record 5
2345, Louis,OPT,N -------record 6

My goal is to select all records in table1 and insert them in table2 of the same structure. Also, if column Type = 'Y', additional duplicate of record 1 is to be added to table2 for a distinct (once for 2067 and once for 2045) value of column Employee ID, let us call new record 1d.

Lastly, values of columns Name and Plan in record id are to change e.g. from Janice Janice2 and LIFE to EHP LIFE.

Hence, the records in table2 could look like:

2067,Janice,LIFE,Y -------record 1
2067,Janice2,EHP LIFE,Y -------record 1d
2067,Janice,MSP,Y -------record 2
2045,Enry,LIFE,Y -------record 3
2045,Enry2, EHP LIFE,Y -------record 3d
2045,Enry,OPT,N -------record 4
2345, Louis,MSP,N -------record 5
2345, Louis,OPT,N -------record 6


Please any help would be appreciated.

 

[Dagon]

I'm not totally clear on the rules for creating the second record. Why don't you create a second record for "Janice, MSP" ? Is that because you only want to do this when the plan is LIFE ? Do you really want to create the extra rows just for employee_ids 2067 and 2045 ?

I'd suggest something like:

insert into table2
select employee_Id,
name,
plan,
type
from table1
union all
select employee_id,
name||'2',
'EHP '||plan,
type
from table1
where type = 'Y'
and employee_id in (2067,2045)
and plan = 'LIFE'

 

[johnherman]

How does this look?

INSERT INTO table2 SELECT * FROM table1
UNION
INSERT INTO table2 SELECT EmployeeID, (Name || '2'), 'EHP LIFE', Type FROM table1 WHERE Type = 'Y' and Plan = 'LIFE'

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[SantaMufasa]

Whoa!!!

John's syntax won't work in the Oracle SQL world. Try this, instead:

INSERT INTO table2 SELECT * FROM table1
union
SELECT EmployeeID, (Name || '2'), 'EHP LIFE', Type FROM table1 WHERE Type = 'Y' and Plan = 'LIFE';

Correct me if I'm wrong.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:00 (26Aug04) UTC (aka "GMT" and "Zulu"), 15:00 (26Aug04) Mountain Time)

 

[johnherman]

I doubt Mufasa is wrong.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright