Search code examples
sqloraclereplacerecordinsert-select

SQL - How to Insert-select with replacing of record id?

I would like to do a insert-select to copy data from one table to another, but also changing (beside others) the record ID. The record_id is a sys_guid so in each line different - how can I replace it whilst copying form one table to another?

What I got now is the following:

insert into TABLE_NEW (record_id, date_time, name, xml_record, meta_data)
   select 
          record_id,
          replace (date_time,'12.02.18','12.03.18'),
          name, 
          replace (xml_record,'cats','dogs'),
          replace (meta_data,'human','alien')
   from 
TABLE_FROM
WHERE ROWNUM <= 500;

Executing this twice wouldn't work as the record_id has a unique constraint ( as well as no null, no default data) - how to overcome this?
PS: it is a oracle database if this might help.

Solution

  • How about using sys_guid()?

    insert into TABLE_NEW (record_id, date_time, name, xml_record, meta_data)
   select sys_guid() as record_id,
          replace(date_time, '12.02.18', '12.03.18'),
          name, 
          replace xml_record,'cats','dogs'),
          replace(meta_data,'human','alien')
   from TABLE_FROM
   where rownum <= 500;