Requirements: batch file to import, if have a primary key to repeat the original record is deleted, to import,
Practice:
1. Read the file after a record, first select the library have, have, delete, and then insert; The efficiency is too low, the file data is big,
2. Insert directly, the primary key repeat to delete, and then insert; Would you please tell me what the problem do?
And what is the most appropriate optimization?

CodePudding user response:

requirements: batch file to import, if have a primary key to repeat the original record is deleted, to import,


From the file to import such operation, you'll have to put the file all records in the pilot into a temporary table, guide with complete, use the unity of the database to merge, finally to empty the temporary table;

Use the merge statement at the time of merger, the building Lord baidu first, there are questions to ask;

CodePudding user response:

The guide into a temporary table, use the merge statement to insert the table again

CodePudding user response:

reference 1st floor wmxcn2000 response:

requirements: batch file to import, if have a primary key to repeat the original record is deleted, to import,


From the file to import such operation, you'll have to put the file all records in the pilot into a temporary table, guide with complete, use the unity of the database to merge, finally to empty the temporary table;

Use the merge statement at the time of merger, the building Lord baidu first, there are questions to ask;

That is my primary key delete and insert again after repeated practice, what is the bad influence? Because version is UAT if the impact is not big, I won't be back version, such as release of the next time again modification,

CodePudding user response:

Judge a times, each record is too time consuming,

If using a temporary table, one-time merge in the past, are much higher efficiency, more than 90%,

CodePudding user response:

reference 4 floor wmxcn2000 response:

judge a times, each record is too time consuming,

If using a temporary table, one-time merge in the past, are much higher efficiency, more than 90%,

Written in C language, embedded SQL can also create a temporary table? Before is used to increase censored

CodePudding user response:

The merge into tableA using (select * from dual) as tableB on (tableA. Id=tableB. Id)
The when matched then update the set...
When not matched then insert value ()

CodePudding user response:

In theory the most scientific way is:
1, put all the data are imported into the build a temporary table
2, and then execute a stored procedure than
Specific logic is: the primary key of the same data records in a single abnormal data in the table, there is no the same directly inserted, finally inserted into log table, the front desk to see whether there is abnormal data and modified to import,

CodePudding user response:

Try: the merge

CodePudding user response:

You try this statement I
DECLARE
CURSOR csr_data IS
SELECT *
FROM A;
The BEGIN
FOR a csr_data IN
LOOP
The UPDATE B B
SET
B. the column name=a. column names//there is a need to update the field
WHERE
B. the column name=a. column names//judge condition
;
END LOOP;
END;

CodePudding user response:

1, the use of external table to read file
2, the building Lord to do the actual operation is UPDATE, so the merge is the best