-
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.
-
Try:
Code:
-- Etc ...
select DECODE(COLUMN_ID,1,' ',',') ||column_name
-- Etc ...
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
Oh, and thanks LKBrwn, that works for me
-
Originally Posted by speedypeavey
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|