Re: INSERT command question

"Tarby777" <nick_williamson_at_mentorg.com> wrote in message news:1128692544.251221.221500_at_g44g2000cwa.googlegroups.com...
> Hi all,
>
> I'm copying from one 9i table to another, and populating a UID column
> at the same time, like this:
>
> INSERT INTO table_1 (col1,col2,uid_column)
> (SELECT col1,col2,SYS_GUID() from table_2);
>
> The system-generated UID is fine, but I need to reformat it for
> application reasons, so I'm taking a second pass through the table
> after the INSERT, like this:
>
> UPDATE table_1 SET uid_column='UID' || SUBSTR(uid_column,1,6) || '-'
> SUBSTR(uid_column,7,11) || '-' || SUBSTR(uid_column,18);
>
> What I'd like to do is reformat the UID in the INSERT command, rather
> than having to have a separate UPDATE command that runs afterwards. Is
> that possible? I appreciate that it's possible within an iterating
> PL/SQL block, but I'd like to do it in a single command if I can...
>
> TIA
> Nick
>

INSERT INTO table_1 (col1,col2,uid_column)

   (SELECT col1,col2,

             'UID' || SUBSTR(sysdual.guid,1,6) || '-'  SUBSTR(sysdual.guid,7,11) || '-' || SUBSTR(sysdual.guid,18)

            from table_2,
   (select SYS_GUID() as guid from dual) sysdual);

I think that would work. Try it on a small test case. Jim Received on Fri Oct 07 2005 - 09:13:01 CDT