Step by step, gathering small streams ------ oracle quick add test data, ------ oracle

Step by step, gathering small streams ------ oracle quick add test data, ------ oracle

Some time ago, problems were found during implementation and need to be modified at that time. Because the data in the database is real data, it cannot be modified. Otherwise, a large leak may occur, therefore, adding test data is easy to modify, and the efficiency of adding a single one is too low, so the solution isAdd new easy-to-delete data to the template with real dataYou can just copy the data of January 1, 2014 and modify the year only. When you delete the data of this non-existent year, you can delete it.

I believe it is easy for you to think of this method and make the answer easily. For example:

Check this table because the primary keys start with the year of the current year, and the year is also the year of the current year, so that we can add and modify the table:

If this table exists as follows:

Btfid, production, code, retrieveid, location, tobaccostation, plantvillage, cooperation, timeout, eastlong, eastlat, southlong, southlat, westlong, westlat, northlong, northlat, amsl, totalarea

In this way, we can write as follows:

insert into arc_basictobaccofieldselect '2016' || substr(btfid, 5),       '2016',       code,       retrieveid,       location,       tobaccostation,       plantvillage,       cooperation,       tobaccotechnician,       eastlong,       eastlat,       southlong,       southlat,       westlong,       westlat,       northlong,       northlat,       amsl,       totalarea  from arc_basictobaccofield where tobaccostation = '37030405C'   and productionyear = 2015

When deleting a table, you only need to delete all the columns whose year is 2016. However, there is still a problem. In fact, the table attributes may not only have such columns, but in fact, even if only these columns are larger than the two columns of data we need to modify, can we solve them better?

The answer is yes. In this way, we can think about it. We only need to modify two columns, so we can extract all the data, after modifying these two columns of data, you can insert them. Let's write it down:

First, create a temporary table:

create table arc_basictobaccofield1 as select * from arc_basictobaccofield where tobaccostation='37030405C' and productionyear=2015

Then we can modify the columns to be modified:

update arc_basictobaccofield1 set productionyear=2015update arc_basictobaccofield1 set btfid ='2015'||substr(btfid,5)

At this time, we will import the modified data into the table we need to import:

insert into arc_basictobaccofield select * from arc_basictobaccofield1

The last step is critical. Never forget: Check the imported data and then delete the temporary table.

drop table arc_basictobaccofield1

So let's look at it again, isn't it necessary to estimate the number of columns in the real table? It can be seen that it is very efficient to think differently.