0

I am exporting data from database for that I have written following query:

SELECT ParameterID, Name,Type,Datatype,LogDate
FROM Param_Ems
WHERE DataType = 10
INTO OUTFILE 'C:/tmp/ParamData.csv'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

Is there any way to export and delete these records within the same query?

7
  • There is no standard SQL (as far as I know ) to achieve this. Are you trying to achieve a better performance or you can't run two queries? Your objective is not clear to me.
    – Masoud
    Jun 22, 2015 at 5:16
  • I want to clear exported data from database after exporting
    – Sush
    Jun 22, 2015 at 5:53
  • Yeah... I understood your question but don't know why are you trying to do this?! because alternatives are not any faster than running the query twice.
    – Masoud
    Jun 22, 2015 at 6:23
  • Because same data will be log into database again.
    – Sush
    Jun 22, 2015 at 6:30
  • I just find the solution in one query if not then i have to make two queries
    – Sush
    Jun 22, 2015 at 6:31

2 Answers 2

0

Use pt-archiver tool. It can delete data that it moves. Alternatively use drop & create after the select into outfile.

0

Put the SELECT INTO OUTFILE and TRUNCATE (or DROP) TABLE in a stored procedure; then CALL the SP.

(Caveat: I am not sure if those two statements can be performed in a SP.)

2
  • 1
    And TRUNCATE does an auto-commit, if I remember right. Jun 22, 2015 at 23:31
  • The commit would probably be OK. If not, the change to a DELETE, which would be slower.
    – Rick James
    Jun 23, 2015 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.