Creating Insert Statement

[wsam]

I am trying to write a script that will create insert statements that will look like:

INSERT INTO t_peep VALUES ('col1', 'col2', 'col3');

The problem that I am having with my select statement is being able to add the single quotes.
Select 'INSERT INTO t_peep VALUES ('<need single quote>col1<need single quote>, etc ||');'

Any hints?

Thanks in advance

 

[BJCooperIT]

To embed a quote in a string you must use 2 quotes, like:

Select 'Dan''s Cafe' from dual;

Hope this helps.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[SantaMufasa]

WSam,

There are several ways to resolve your need:

Option 1, Traditional, &quot;Two single quotes result in one single quote&quot; method:

...

Option 2, &quot;Concatenate CHR(39) [']&quot; method:

Select 'INSERT INTO t_peep VALUES ('||chr(39)||'col1-value'||chr(39)||','||chr(39)||'col2-value'||chr(39)||','||chr(39)||'col3-value'||chr(39)||');' from ...

Option 3, &quot;Translate 'back-quotes' into 'standard quotes'&quot; method:

Select translate('INSERT INTO t_peep VALUES (`col1-value`,`col2-value`,`col3-value`);',chr(96),chr(39)) from ...

I personally use Option 3 when Option 1 becomes/appears confusing.

Let me know if this resolves your problem.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:13 (10Jan04) GMT, 11:13 (10Jan04) Mountain Time)

 

[SantaMufasa]

WSam,

Option 1 looks pretty clever, huh? It should have read:

Option 1, Traditional, &quot;Two single quotes result in one single quote&quot; method:

Select 'INSERT INTO t_peep VALUES (''col1-value'',''col2-value'',''col3-value'');' from ...

(This is the method that BJCooperIT posted while I was composing my post. Good call, BJ ).

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:17 (10Jan04) GMT, 11:17 (10Jan04) Mountain Time)