Hi ppl

I have a bunch of records in tableA that I want to put in tableB.
In tableA there is a column called 'Role' and it has user roles such
as 'staff', 'student', 'admin', etc. Because there is replication of
those roles in many rows, I do "select distinct Role from tableA".
I want unique records (for the roles) in tableB.

So my question is, can I write one SQL statement that gets all the
distinct values of tableA and puts them in tableB? I.e I don't want to
select all the distinct roles into a cursor and then iterate thru
that, inserting 1 at a time (unless that's the only option). To put it
another way, can I execute it in one hit (i.e rather than run a query
to get a cursor then iterate thru that cursor inserting one record at
a time).

Effectively my starting approach would be something like:

insert into tableB (tableB.role) values (tableA.role) where
(tableA.role not in (select tableB.role from tableB) ) AND
(select distinct tableA.role from tableA)

I know this is wrong, but this more-or-less what I'm trying to achieve...