Database

DECLARE
N NUMBER(10);
BEGIN
    SELECT MAX(ID)+1 INTO N FROM TEST;
-- CREATING SEQUENCE
Execute immediate ('DROP SEQUENCE SEQ_TEST');
Execute immediate ('CREATE SEQUENCE SEQ_TEST
    MINVALUE 1
    START WITH '||N||'
    INCREMENT BY 1
    CACHE 20
    ORDER');
END;
/

CREATE OR REPLACE TRIGGER TRG_TEST_AUTONUMBER 
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
  SELECT SEQ_TEST.NEXTVAL
  INTO   :NEW.ID
  FROM   DUAL;
END;
/

INSERT INTO TEST(NAME,YEAR)
SELECT NAME,2009 FROM TEST WHERE YEAR = 2008
/

DECLARE
  ID NUMBER;
BEGIN
  SELECT MAX(ID) INTO ID FROM TEST;
  
  EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_TEST
  MINVALUE 1
  START WITH ' || ID || '
  INCREMENT BY 1
  CACHE 20
  ORDER';
END;
/

tolucole wrote:

See the SQL Server errorlog for more information.

insert into tablename<br />
select name,'2009' from tablename where [year]='2008'

SQL> DESC TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(10)
 YEAR                                               NUMBER(4)

SQL> 

SQL> INSERT INTO TEST
  2  SELECT NAME,2009 FROM TEST WHERE YEAR=2008
  3  /
INSERT INTO TEST
            *
ERROR at line 1:
ORA-00947: not enough values

CREATE SEQUENCE SEQ_TEST
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20
    ORDER
/

CREATE OR REPLACE TRIGGER TRG_TEST_AUTONUMBER 
BEFORE INSERT ON TEST
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
  SELECT SEQ_TEST.NEXTVAL
  INTO   :NEW.ID
  FROM   DUAL;
END;
/