How to Compare one column of a table to another column of a table ? (Urgent !!!)

[bsuroop]

Hi Friends,

I need to compare one column of a table with another column of a table. For Instance I have a temporary staging table with 25 columns. Each column is relating to another table. I have to compare these columns and if the same value exists I need to skip it and if it is different value I need to insert it. Everything I need to keep in a stored procedure. Pls help me how I can do this in an efficient way and faster performance.

Example
-------

Temporary Table
---------------
Name
City
State
Country
-
-
-


Persons Table
-------------
Name

City Table
----------
City

State Table
-----------
State

Country Table
-------------
Country

Now I need to check each column in the Staging table with respective tables like City, State, Country tables.

Pls give me your suggestions,

Thanks in Advance

Kishan

[janjacina]

1. solution

INSERT INTO persons
SELECT name FROM temporary_table
WHERE name NOT IN (SELECT name FROM persons);

COMMIT;

2. solution

SELECT name FROM temporary_table
WHERE name NOT IN (SELECT name FROM persons)
BULK COLLECT INTO collection;

FORALL i IN collection.FIRST..collection.LAST
INSERT INTO persons (name) VALUES (collection(i));

COMMIT;

[bsuroop]

Thank You very Much. You are great. !! I am a newbie.Can you pls help me how to put it in a stored procedure with the above 2 example.Pls give me the syntax for the script and how to execute it.

Kishan

[janjacina]

I havn't tried it but should work.


CREATE OR REPLACE PROCEDURE migration
AS
TYPE tp_string IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
col_names tp_string;
BEGIN
-- 1. solution
INSERT INTO persons
SELECT name,...,... FROM temp_table
WHERE name NOT IN (SELECT name FROM persons);
COMMIT;

-- etc. the same with cities...


-- 2.solution
SELECT name FROM temp_table WHERE name NOT IN (SELECT name FROM persons)
BULK COLLECT INTO col_names;

FORALL i IN col_names.FIRST..col_names.LAST
INSERT INTO persons (name,...,...) VALUES (col_names(i),...,...);

COMMIT;

-- etc. the same with cities...

END;