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?

  • 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 '15 at 5:16
  • I want to clear exported data from database after exporting – Sush Jun 22 '15 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 '15 at 6:23
  • Because same data will be log into database again. – Sush Jun 22 '15 at 6:30
  • I just find the solution in one query if not then i have to make two queries – Sush Jun 22 '15 at 6:31
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.)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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