SQL Reporting Services

PROCEDURE procDeptMigration
IS
--
-- Cursor to retrieve all the old dept data
--
CURSOR cu_dept IS
SELECT * FROM dept;
crec_dept cu_dept%ROWTYPE;

BEGIN

FOR crec_dept IN cu_dept
LOOP

--insert in the new table
INSERT INTO DEPARTMENT
VALUES
(crec_dept.deptno, crec_dept.dname, crec_dept.loc);

-- Display the entire able on screen
DBMS_OUTPUT.PUT_LINE('DEPTNO : '||crec_dept.deptno );
DBMS_OUTPUT.PUT_LINE('DNAME : '||crec_dept.dname );
DBMS_OUTPUT.PUT_LINE('LOC : '||crec_dept.loc );

END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM||' AddressCode '||crec_dept.deptno);

END procDeptMigration;

create table test (id int identity(1,1), code char(5))
insert into test (code) values ('a')
insert into test (code) values ('a')
insert into test (code) values ('a')
insert into test (code) values ('b')
insert into test (code) values ('b')
insert into test (code) values ('b')
insert into test (code) values ('c')
insert into test (code) values ('d')
insert into test (code) values ('d')

--Identify the rows
select code from test group by code having count(*) > 1

--Remove the rows
delete test
where id > (
select min(m.id)
from test m
where m.code = test.code
)