CODEWITHSUNDEEP
sqloracle-databasereplacerecordinsert-select
m.weiloa
m.weiloa

Reputation: 165

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.

Upvotes: 0

Views: 633

Answers (2)

Ctznkane525
Ctznkane525

Reputation: 7465

Replace it with this

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 b
WHERE ROWNUM <= 500 and not exists (select a.record_id from table_new a where a.record_id=b.record_id)

The exists statement checks if it's in the new table

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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;

Upvotes: 2

Related Questions