Home > Database, Oracle > Create Insert Statement for Table Data

Create Insert Statement for Table Data

In every site we have more than one environment  as Development, System Test, Integration Test, UAT, Pre-Production, Production, …..etc). Sometimes we need to insert some data in one environment and want to migrate it to another  environment. And what if we don’t have DBA rights. Under this condition, we can’t migrate any data from one environment to another. And,Usually we use database editors to do this task like (Toad, Plsql Developer, SQL developer, …. etc), but in this post , you can find a custom function which is called GEN_INSERT_STATEMENT and which is used for returning “SQL select  statement against input table parameter which we can use it to generate insert statement”.

CREATE OR REPLACE FUNCTION GEN_INSERT_STATEMENT (IN_TABLE_NAME VARCHAR2)  
   RETURN VARCHAR2  
 IS  
   LC$COLS_SELECT     VARCHAR2 (4000);  
   LC$COLS_VALUES     VARCHAR2 (4000);  
   LC$COLOUMN      VARCHAR2 (200);  
   CURSOR LCUR$TAB_COLUMNS (IN_TABLE_NAME VARCHAR2)  
   IS  
     SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID  
      FROM USER_TAB_COLS  
      WHERE TABLE_NAME = IN_TABLE_NAME  
    ORDER BY COLUMN_ID;  
 BEGIN  
   FOR LREC$TAB_COLUMNS IN LCUR$TAB_COLUMNS (UPPER (IN_TABLE_NAME))  
   LOOP  
    LC$COLS_SELECT :=  
       LC$COLS_SELECT  
      || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END  
      || LREC$TAB_COLUMNS.COLUMN_NAME;  
    IF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'CHAR') > 0  
    THEN  
      LC$COLOUMN :=  
       '''''''''||' || LREC$TAB_COLUMNS.COLUMN_NAME || '||''''''''';  
    ELSIF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'DATE') > 0  
    THEN  
      LC$COLOUMN :=  
       '''TO_DATE(''''''||TO_CHAR(' || LREC$TAB_COLUMNS.COLUMN_NAME  
       || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';  
    ELSE  
      LC$COLOUMN := LREC$TAB_COLUMNS.COLUMN_NAME;  
    END IF;  
    LC$COLS_VALUES :=  
       LC$COLS_VALUES  
      || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END  
      || '''||DECODE('  
      || LREC$TAB_COLUMNS.COLUMN_NAME  
      || ',NULL,''NULL'','  
      || LC$COLOUMN  
      || ')||''';  
   END LOOP;  
   RETURN  'SELECT ''INSERT INTO '  
      || IN_TABLE_NAME  
      || ' ('  
      || LC$COLS_SELECT  
      || ') VALUES ('  
      || LC$COLS_VALUES  
      || ');'' FROM '  
      || IN_TABLE_NAME  
      || ';';  
 END;

Let’s Now run function for table EMPLOYEES.

SELECT GEN_INSERT_STATEMENT('EMPLOYEES') FROM DUAL;

The output is select statement that we should run it to get insert statement of data. Which is :

SELECT ‘INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)VALUES (‘||DECODE(EMPLOYEE_ID,NULL,’NULL’,EMPLOYEE_ID)||’,’||DECODE(FIRST_NAME,NULL,’NULL’,””||FIRST_NAME||””)||’,’||DECODE(LAST_NAME,NULL,’NULL’,””||LAST_NAME||””)||’,’||DECODE(EMAIL,NULL,’NULL’,””||EMAIL||””)||’,’||DECODE(PHONE_NUMBER,NULL,’NULL’,””||PHONE_NUMBER||””)||’,’||DECODE(HIRE_DATE,NULL,’NULL’,’TO_DATE(”’||TO_CHAR(HIRE_DATE,’mm/dd/yyyy hh24:mi’)||”’,”mm/dd/yyyy hh24:mi”)’)||’,’||DECODE(JOB_ID,NULL,’NULL’,””||JOB_ID||””)||’,’||DECODE(SALARY,NULL,’NULL’,SALARY)||’,’||DECODE(COMMISSION_PCT,NULL,’NULL’,COMMISSION_PCT)||’,’||DECODE(MANAGER_ID,NULL,’NULL’,MANAGER_ID)||’,’||DECODE(DEPARTMENT_ID,NULL,’NULL’,DEPARTMENT_ID)||’);’ FROM EMPLOYEES;

And after running this statement. you will get your insert script as below:

Image

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: