Remove last comma in a dynamic insert statement

[speedypeavey]

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.

[LKBrwn_DBA]

Try:

-- Etc ...
select DECODE(COLUMN_ID,1,' ',',') ||column_name
-- Etc ...

[PAVB]

Load the column names into a cursor then print them from there, print comma separately before the column name and not after. You just have to make sure you do not print the comma for the first item of the cursor.

Out of curiosity... is this insert supposed to insert on the same table? like a serpent eating it's own tail?

[speedypeavey]

Hi PAVB,

Doesn't that just reverse my problem? Still need to remove the pesky comma from the start, rather than the end.

Yes, this inserts into the same table. I'm using it to duplicate a row within a table of many columns, but I need to alter the primary key, and specific data within the row.

[speedypeavey]

Oh, and thanks LKBrwn, that works for me

[PAVB]

Doesn't that just reverse my problem? Still need to remove the pesky comma from the start, rather than the end.

Nope. It doesn't. If you read carefully "You just have to make sure you do not print the comma for the first item of the cursor" which is an easy thing to do since you know when you are fetchng the first one.