How can I insert rows in a new table from another table based on some condition in Sybase?

Question

I need to create a stored procedure to insert data into one table from another based on some conditions. In the existing table, if two columns (primary role and secondary role) have the same value, then I want just one row in the new table with role as primary role.

In case if a row in the old table has different values in primary role and secondary role, I want two rows in the new table, one having the value of role as primary role of old table, and another as secondary.

What is the best way to achieve this?

Right now my query looks something like this

create procedure proc as
begin
  insert into newTable values(role)
    select primary_role as role from oldTable
    where primary_role = secondary_role
end

This does not handle the case where primary role is not the same as secondary role.

Sample

sample row oldTable

PrimaryRole | SecondaryRole | Name

admin       | analyst       | Sara

sample row newTable

Role    | Name

admin   | Sara

analyst | Sara

Answer 1

I'm not a sybase expert, but I would do something like this (syntax may need amending). Also, if you are after performance, this can probably be done much more cleverly.

  insert into newTable values(role, name) (
    select primary_role as role, name from oldTable
    where primary_role+name not in (select distinct role+name from newTable)
  )

  insert into newTable values(role, name) (
    select secondary_role as role, name from oldTable
    where secondary_role+name not in (select distinct role+name from newTable)
  )

This will obviously run 2 different inserts, which is why it could probably be made more performant, but it will essentially try to add all primary roles and all secondary roles, checking to see if the role already exists from the previous run. So no need to check for the case where primary = secondary.

EDIT

Alternatively, you may be able to use UNION ALL:

  insert into newTable values(role, name) (
    select distinct role, name from (
      select primary_role as role, name from oldTable
      union all
      select secondary_role as role, name from oldTable
    )
  )