CODEWITHSUNDEEP
sqloracle-databasefunctionreplaceall
Marcus
Marcus

Reputation: 3869

Advance Replace in insert scripts

I have below 2 insert statements which i took the export from sql developer from dev environment. I have delete those records from dev afterwards. Now i want to run this insert statement again in dev because those are my back up but i am getting error as virtual column which is ORD_DAYID cannot be used inside insert script. So i want to exclude this column and also the respective values using replace function or any tools which i dont know. I didnt know previously that i have virtual column for this table. I would like to know is there any tool or function where i can select ORD_DAYID and also the respective values get selected and then i can delete those and then i can be able to run this insert statement again in test enviornment.

P.S i have mentioned only 2 sample insert statements but there are 1000 insert statements. So its very difficult to manually delete this ORD_DAYID from this insert statements with respective values.

Insert into test_ord (IS_GRP,ORD_DAYID,REF_CAMPA_CODE) values (1,20150813,null);
Insert into test_ord (IS_GRP,ORD_DAYID,REF_CAMPA_CODE) values (1,20150828,null);

Upvotes: 0

Views: 452

Answers (2)

miracle173
miracle173

Reputation: 1973

  1. create an auxiliary table without virtual columns.
  2. Restore your data to this auxiliary table.
  3. Transfer the data from the auxiliary table to the original table.
    -- this is your table
    create table mytab(A number, b number, s as (a+b));
    --fill it with data
    insert into mytab(a,b) values(1,1);
    insert into mytab(a,b) values(1,2);
    insert into mytab(a,b) values(2,1);
    insert into mytab(a,b) values(2,2);
    commit;
    -- check its content
    select * from mytab;
    -- now delete the rows
    delete from mytab;
    commit;

    -- restore your data
    --------------------

    -- create a table similar the table you want to restore
    --   but the virtual colums as regular columns.
    create table ctas as 
        select * from mytab where 1!=0;

    -- insert your backup data
    insert into ctas(a,b,s) values(1,1,2);
    insert into ctas(a,b,s) values(1,2,3);
    insert into ctas(a,b,s) values(2,1,3);
    insert into ctas(a,b,s) values(2,2,4);
    commit;

    -- transfer the data to the table you want to restore
    insert into mytab(a,b) select a,b from ctas;

Upvotes: 0

APC
APC

Reputation: 146239

You can edit your INSERT statements using regular expressions, in an editor such as Notepad++.

So to change this ...

Insert into test_ord (IS_GRP,ORD_DAYID,REF_CAMPA_CODE) values (1,20150813,null);

... into this ...

Insert into test_ord (IS_GRP,REF_CAMPA_CODE) values (1,null);

You need a search pattern of:

Insert into test_ord \(IS_GRP,ORD_DAYID,REF_CAMPA_CODE\) values \(([0-9]+),([0-9]+),null\);

and a replace pattern of:

Insert into test_ord \(IS_GRP,REF_CAMPA_CODE\) values \(\1,null\);

Obviously you will need to refine the search pattern to cater for all the different values of IS_GRP, and REF_CAMPA_CODE in your 1000 statements.

" is there any way where we can count the place of column and value and replace it with null"

No. The snag with virtual columns is that they cannot be referenced in INSERT or UPDATE statements. So you need to totally exclude it from the projection.

"i am not able to find those option in notepad++"

Really? Search and replace is not an exotic option:

  • From the menu: Search > Find > Replace [tab] (or [ctrl]+h)
  • As the search mode select the regular expression radio button

Notepad++ regex search'n'replace

Upvotes: 1

Related Questions