Remove last comma in a dynamic insert statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Remove last comma in a dynamic insert statement

  1. #1
    Join Date
    Feb 2012
    Posts
    5

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,441

    Wink

    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

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Feb 2012
    Posts
    5
    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.

  5. #5
    Join Date
    Feb 2012
    Posts
    5
    Oh, and thanks LKBrwn, that works for me

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by speedypeavey View Post
    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
  •  



Click Here to Expand Forum to Full Width