CREATE TABLE T_EXTERNAL EXTERNAL FILE 'C:\TEMP\DONNEES.TXT' (
    CIV            char( 1),
    NAME        char(40),
    FIRSTNAME        char(30),
    DESCRIPT        char(60),
    RT            char(2)
);

ALTER TABLE CLIENT DROP CONSTRAINT PK_CLIENT;
ALTER TABLE CLIENT DROP CONSTRAINT FK_CLIENT_CIV;
DROP INDEX IDX_NOM_ASC;
DROP INDEX IDX_NOM_DESC;
DROP INDEX IDX_IDCLT_DESC;

CREATE PROCEDURE PS_TRANSFERT
AS
    DECLARE VARIABLE CIV        Char(1) ;
    DECLARE VARIABLE NAME        Char(40);
    DECLARE VARIABLE FIRSTNAME    Char(30);
    DECLARE VARIABLE DESCRIPT    Char(60);
BEGIN
    FOR
        SELECT CIV,NAME,FIRSTNAME,DESCRIPT
        FROM T_EXTERNAL
        INTO :CIV, :NAME, :FIRSTNAME, :DESCRIPT 
    DO
    BEGIN
        /*Insérer ici les Vérifications ou mises en forme */
        NAME = UPPER(NAME);

        INSERT INTO CLIENT (IDCLIENT,CIVILID,NOM,PRENOM,DESCRIPT)
        VALUES (null,:CIV,:NAME,:FIRSTNAME,:DESCRIPT);
    END
END;

EXECUTE PROCEDURE PS_TRANSFERT;

DROP PROCEDURE    PS_TRANSFERT;
DROP TABLE        T_EXTERNAL;

CREATE DESCENDING INDEX IDX_IDCLT_DESC ON CLIENT (IDCLIENT);
CREATE ASCENDING INDEX IDX_NOM_ASC ON CLIENT (NOM);
CREATE DESCENDING INDEX IDX_NOM_DESC ON CLIENT (NOM);

ALTER TABLE CLIENT ADD CONSTRAINT FK_CLIENT_CIV FOREIGN KEY (CIVILID) REFERENCES CIVILITE (IDCIVILITE);
ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (IDCLIENT);