insert into (select) with oracle sequence

Colleague needed some SQL today for an upgrade script. Needed to add default data to entities that didn’t have a specific parameter.

As usual I reduced the problem to a snippet. Although he was trying to squeeze it into a single command with INSERT INTO (SELECT…), due to time constraints I felt a stored proc was faster to get over it. Maybe someone can post the single command here.

In this case the TestTable already had some data, with refids 1, 2, and 3. Ref 2 already had the xyz param, but the others don’t. So the task is to add the field to all other refids, using the given TestSeq.


CREATE SEQUENCE TestSeq START WITH 7;

CREATE TABLE TestTable (
	id NUMBER PRIMARY KEY,
	refid NUMBER,
	param VARCHAR2(64),
	other NUMBER
);

INSERT INTO TestTable VALUES(1, 1, 'abc', 0);
INSERT INTO TestTable VALUES(2, 1, 'def', 0);
INSERT INTO TestTable VALUES(3, 2, 'abc', 0);
INSERT INTO TestTable VALUES(4, 2, 'xyz', 0);
INSERT INTO TestTable VALUES(5, 3, 'abc', 0);
INSERT INTO TestTable VALUES(6, 3, 'def', 0);

DECLARE
	newid TestTable.id%type;
	CURSOR dataCursor IS
		SELECT UNIQUE refid, other FROM TestTable WHERE refid NOT IN 
		(SELECT refid FROM TestTable WHERE param = 'xyz');
BEGIN
	FOR dataRow IN dataCursor
	LOOP
		SELECT TestSeq.nextval INTO newid FROM DUAL;
		INSERT INTO TestTable VALUES(newid, dataRow.refid, 'xyz', dataRow.other);
	END LOOP;
END;
/

RSS feed for comments on this post · TrackBack URL

Leave a Comment