Generate Insert and Update Statements for Excel Sheet From SQL Query in Oracle

Generate Insert and Update SQL statements from Excel sheet is easy but sometimes seems very boring and tedious job, if table is having many columns and you need to do this for many Excel sheets to import the data into Oracle. Because there is also need to put lot of quotes and ampersands (&) and if you will skip any double quote or single quote then the statement will give error.

So I tried to create a SQL Query in Oracle to generate these Insert and Update statements for Excel sheet and once the statement will be generated, just paste the statement into Excel sheet cell and your statement is created.

You just need to change the table name in the query and run the SQL query into the schema where that table exists.

Below is the example to generate Insert statement for Excel:

Suppose you have data in Excel sheet like this for EMP table and you want to import this data into the Emp table.

Generate insert and update statements from Excel sheet to import data

For this just execute the below query in your schema where the table exists, for this I used Scott schema and change the table name which is highlighted with yellow to your table name. You can also change the Excel row number which is also highlighted below as 2 if your Excel data is starting from any other row.

SELECT    '='
       || CHR (34)
       || 'Insert into '
       || 'EMP'
       || '('
       || listagg (column_name, ',') WITHIN GROUP (ORDER BY r)
       || ') Values ('
       || listagg (
                CHR (39)
             || CHR (34)
             || '&'
             || excel_col
             || '2&'
             || CHR (34)
             || CHR (39),
             ',')
          WITHIN GROUP (ORDER BY r)
       || ');'||chr(34)
  FROM (  SELECT column_name,
                 CASE
                    WHEN r > 26 AND r <= 52 THEN 'A'
                    WHEN r > 52 AND r <= 78 THEN 'B'
                    ELSE ''
                 END
                 || SUBSTR (
                       'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                       R,
                       1)
                    EXCEL_COL,
                 table_name,
                 r
            FROM (  SELECT column_name, table_name, ROWNUM R
                      FROM user_tab_cols
                     WHERE table_name = 'EMP'
                  ORDER BY column_id)
        ORDER BY r);

And the statement will be generated like this below:

="Insert into EMP(DEPTNO,COMM,SAL,HIREDATE,MGR,JOB,ENAME,EMPNO) 
Values ('"&A2&"','"&B2&"','"&C2&"','"&D2&"','"&E2&"','"&F2&"','"&G2&"','"&H2&"');"

Just paste this statement into the Excel sheet cell and pull the fill handle to create statement for all the rows. And now your insert statements with data are ready something like this below:

INSERT INTO EMP (DEPTNO,
                 COMM,
                 SAL,
                 HIREDATE,
                 MGR,
                 JOB,
                 ENAME,
                 EMPNO)
     VALUES ('7369',
             'SMITH11V9',
             'CLERK',
             '7698',
             '21jun1990',
             '800',
             '',
             '20');

Same is for Update statement below, just change the table name which is highlighted and run the query and your Update statement is ready for Excel.

select '='
       || CHR (34)
       || 'Update '
       || 'EMP'
       || ' Set ' || listagg(col, ', ') within group (order by r) || ' where '||listagg(col, 'and ') within group (order by r) ||';'||chr(34) from (
SELECT    column_name
       || ' = '
       || CHR (39)
       || CHR (34)
       || '&'
       || excel_col
       || '2&'
       || CHR (34)
       || CHR (39)
       col, r
  FROM (  SELECT column_name,
                 CASE
                    WHEN r > 26 AND r <= 52 THEN 'A'
                    WHEN r > 52 AND r <= 78 THEN 'B'
                    ELSE ''
                 END
                 || SUBSTR (
                       'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ',
                       R,
                       1)
                    EXCEL_COL,
                 table_name,
                 r
            FROM (  SELECT column_name, table_name, ROWNUM R
                      FROM user_tab_cols
                     WHERE table_name = 'EMP'
                  ORDER BY column_id)
        ORDER BY r));

After running the above query the update statement will be generated like below:

="Update EMP Set EMPNO = '"&A2&"', ENAME = '"&B2&"', JOB = '"&C2&"', MGR = '"&D2&"', 
HIREDATE = '"&E2&"', SAL = '"&F2&"', COMM = '"&G2&"', DEPTNO = '"&H2&"' where EMPNO = 
'"&A2&"'and ENAME = '"&B2&"'and JOB = '"&C2&"'and MGR = '"&D2&"'and HIREDATE = '"&E2&"'and
 SAL = '"&F2&"'and COMM = '"&G2&"'and DEPTNO = '"&H2&"';"

And after when you will paste the above statement into Excel it will become like this below:

UPDATE EMP
   SET EMPNO = '7369',
       ENAME = 'SMITH11V9',
       JOB = 'CLERK',
       MGR = '7698',
       HIREDATE = '21jun1990',
       SAL = '800',
       COMM = '',
       DEPTNO = '20'
 WHERE     EMPNO = '7369'
       AND ENAME = 'SMITH11V9'
       AND JOB = 'CLERK'
       AND MGR = '7698'
       AND HIREDATE = '21jun1990'
       AND SAL = '800'
       AND COMM = ''
       AND DEPTNO = '20';

You might need to modify the where clause because I used all the columns as criteria.

This utility SQL helped me and I think this will be helpful for you also. Thanks.