如何将存储过程的输出存储到磁盘上 [英] How to store output of a stored procedure on to disk

问题描述

我们正在使用SQL Server 2005.
我想创建存储过程的输出文件。

如果您使用的是MySQL，您可能需要从以下开始：

  DELIMITER $$ 
 
 CREATE PROCEDURE export_dynamic（IN file_path char（64））
 BEGIN 
 SET @sql = CONCAT（'SELECT * INTO OUTFILE'，
'，file_path，'， FROM Table1'）; 
 
 PREPARE stmt1 FROM @sql; 
 EXECUTE stmt1; 
 DEALLOCATE PREPARE stmt1; 
 END $$ 
 
 DELIMITER; 
  

借阅：

如果您想在存储过程中对文件名进行硬编码，则不需要使用准备好的语句：

  DELIMITER $$ 
 
 CREATE PROCEDURE export_static（）
 BEGIN 
 SELECT * INTO OUTFILE'/tmp/file_name.txt'FROM Table1; 
 END $$ 
 
 DELIMITER; 
  

如果您得到无法创建/写入文件错误，请务必检查以下参考： MySQL 5.1参考手册::无法创建/写入文件。

We are using SQL Server 2005. I want to create the file of the output of the stored procedure.

解决方案

If you are using MySQL you may want to start from the following:

DELIMITER $$

CREATE PROCEDURE export_dynamic(IN file_path char(64))
BEGIN
    SET @sql = CONCAT('SELECT * INTO OUTFILE ', 
                      "'", file_path, "'", ' FROM Table1');

    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END $$

DELIMITER ;

Borrowed from: MySQL Forums :: General :: SELECT .. INTO OUTFILE not possible with stored procedure parameters?

If you want to hardcode the filename in the stored procedure, then there is no need to use a prepared statement:

DELIMITER $$

CREATE PROCEDURE export_static()
BEGIN
    SELECT * INTO OUTFILE '/tmp/file_name.txt' FROM Table1;
END $$

DELIMITER ;

If you get a Can't create/write to file error, make sure to check the following reference: MySQL 5.1 Reference Manual :: Can't create/write to file.

这篇关于如何将存储过程的输出存储到磁盘上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！