有没有办法直接压缩/拉链来自SQL查询的结果？ [英] Is there a way to directly compres/zips the result from a SQL query?

问题描述

后来我一直在使用SSMS转储相对较大的表。通常的方法是设置 Query-> Results-To-> File ，'Execute`，选择一个文件并让SQL查询运行。完成后，我通常压缩文件，然后将其传输到我的本地机器。这有明显的问题，主机在空间SQL查询期间耗尽空间。

Off late I have been dumping relatively large tables using SSMS. The usual way is to set Query->Results-To->File, 'Execute`, choose a file and let the SQL query run. After it finishes, I usually zip the file and then transfer it to my local machine. This has obvious problems of the host machine running out of space during overnight SQL queries.

我想知道是否有一种方法直接压缩SSMS的输出，而不必请等待，直到它从整个查询转储结果。有什么建议么？主机是非常受限制的，它允许我运行它，所以一个建议，需要最小的第三方软件是伟大的。

I was wondering if there is a way to compress the output from SSMS directly without having to wait until it dumps the results from the entire query. Any suggestions? The host machine is pretty restricted in what it allows me to run on it so a suggestion that requires minimal third-party software would be great.

推荐答案

从 sqlcmd 运行查询，然后将输出转换为命令行zip（您需要安装一个，请参阅什么是好的tar实用程序？）。或者你可以使用PowerShell，可以拉开箱子，包括管道输入，请参阅使用Windows PowerShell压缩文件，然后打包Windows Vista边栏小工具，附加工具作为PS已经在您的主机服务器上（虽然在第二次读取我认为PS解决方案，如在链接，仍然需要一个放气的文件，不能压缩文件）。

Run the queries from sqlcmd instead and pipe the output into a command line zip (you'll need to install one, see What's a good tar utility for Windows?). Or you can use PowerShell that can zip out-of-the-box, including piped input, see Compress Files with Windows PowerShell then package a Windows Vista Sidebar Gadget, this requires no additional tools as PS is already on your host server (although on second read I think the PS solutions, as in the link, still requires a deflated file first, cannot compress on-the-file).

使用sqlcmd和7zip执行示例查询：

Sample query using sqlcmd and 7zip:

sqlcmd -S <DATABASE> -s <COLUMNSEP> -Q "SELECT ..." | .\7za.exe a -si <FILENAME>

记住使用 -Q 查询和退出），而不是 -q （运行查询），否则这将无法工作。

Remember to use the -Q (run query and exit) and not the -q (run query) or else this won't work.

这篇关于有没有办法直接压缩/拉链来自SQL查询的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！