Accumulate Kuibu, Poly stream------Oracle Quick Add test data

A while ago in the implementation of the problem, need to be modified, and because the data in the database is real data, can not be modified, otherwise it will be a big mistake, so add test data to facilitate the modification, and single add efficiency is too low, so the solution is Add new and easy-to-delete data to the template with real data , just like copying a copy of the 2014 data to only modify the year and delete it when the non-existent year data is deleted.

I believe it is easy to think of this method and it is easy to make an answer, for example:

Look at this table, because the primary key is all beginning with the year of the year, and the year is the year, so we can add the changes:

If the form is said to exist as follows:

Btfid, production, code, Retrieveid, location, tobaccostation, Plantvillage, cooperation, Tobaccotechnician, Eastlong, Eastlat, Southlong, Southlat, Westlong, Westlat, Northlong, Northlat, AMSL, Totalarea

So we can write this:

INSERT into Arc_basictobaccofieldselect ' 2016 ' | | SUBSTR (Btfid, 5),       ' $ ',       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

Then delete all you need to delete the year is 2016, but there is still a problem, in fact, the properties of the table may not only this point column, in fact, even if only these columns, is far more than we need to modify the two columns of data, we can have a better way to solve it?

The answer is yes, so we think upside down, we just need to change the two columns, then we take all the data out, the two columns of data modified after the insertion is not okay, let's write down the look:

Let's start by creating a temporary table:

CREATE TABLE Arc_basictobaccofield1 as SELECT * from Arc_basictobaccofield where tobaccostation= ' 37030405C ' and Productio nyear=2015

Then we'll modify the columns that need to be modified:

Update arc_basictobaccofield1 set productionyear=2015update arc_basictobaccofield1 Set Btfid = ' 2015 ' | | SUBSTR (btfid,5)

This is the time to import the modified data into the table we need to import:

INSERT INTO Arc_basictobaccofield select * from Arc_basictobaccofield1

The final step is still critical, and never forget: Check the import data, and then delete the temporary table

drop table Arc_basictobaccofield1

In this way, it is not necessary to estimate the real table how many columns, it can be seen that many times a different way of thinking is still very efficient

Accumulate Kuibu, Poly stream------Oracle Quick Add test data