Remove last comma in a dynamic insert statement
Hi Guys,
I've butchered this code together to create a dynamic table insert from the same table (so that I can amend specific column data also):
---------------------------------------
set pages 0
set lines 40
set head off
set verify off
set echo off
set feedback off
PROMPT
PROMPT
ACCEPT TABLE_NAME PROMPT 'Please insert the SOURCE Table Name: '
PROMPT
PROMPT
select
'insert into '||UPPER('&TABLE_NAME')||'('
from dual;
select column_name ||','
from all_tab_columns
where table_name = upper('&TABLE_NAME')
order by column_id;
select ' ) select '
from dual;
select column_name ||','
from all_tab_columns
where table_name = upper('&TABLE_NAME')
order by column_id;
select ' from '||'&TABLE_NAME'||';'
from dual;
undefine TABLE_NAME
-----------------------------------------------
Which produces this (for instance):
insert into DIARIES_TEXTS(
DIATX_LAST_CHANGE_BY,
DIATX_LAST_CHANGE_DATE,
DIATX_DIA_SEQNO,
DIATX_SOC_SEQNO,
DIATX_SEQNO,
DIATX_TEXT,
) select
DIATX_LAST_CHANGE_BY,
DIATX_LAST_CHANGE_DATE,
DIATX_DIA_SEQNO,
DIATX_SOC_SEQNO,
DIATX_SEQNO,
DIATX_TEXT,
from diaries_texts;
-------------------------------------
How do I remove those pesky red commas?
Many thanks.