如何在 SQLCMD 中抑制连字符
如何在此 sqlcmd 命令的结果集中隐藏连字符 (------------):
C:\temp>sqlcmd -d AdventureWorks -s ";"
-Q "SET NOCOUNT ON SELECT top 5 FirstName, LastName FROM Person.Contact;"
FirstName ;LastName
--------------------------------------------------;----------------------------
Gustavo ;Achong
Catherine ;Abel
Kim ;Abercrombie
Humberto ;Acevedo
Pilar ;Ackerman
C:\temp>
How can I suppress hyphens (------------) from the results set of this sqlcmd
command:
C:\temp>sqlcmd -d AdventureWorks -s ";"
-Q "SET NOCOUNT ON SELECT top 5 FirstName, LastName FROM Person.Contact;"
FirstName ;LastName
--------------------------------------------------;----------------------------
Gustavo ;Achong
Catherine ;Abel
Kim ;Abercrombie
Humberto ;Acevedo
Pilar ;Ackerman
C:\temp>
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(19)
从结果中删除破折号:
remove dashes from results:
我通常按如下方式处理生成的
.csv
文件:其中:
-i
就地编辑文件...2d
删除第 2 行,在这种情况下,破折号线...$d
删除文件末尾的单行..."$file"
是保存路径的 bash 变量字符串到.csv
文件。I typically process the resulting
.csv
file as follows:Where:
-i
edits the file in-place...2d
deletes line 2, in this case, the lines of dashes...$d
deletes single line at the end of the file..."$file"
is the bash variable holding the path string to the.csv
file.这就是我最终得到的结果并且对我来说效果很好:
我相信在这一点上,findstr &正则表达式是所有需要解释的内容:
正则表达式指出“在开始
^
时,有 - 或 ,[-,]
任意次数*< /code>,直到到达行尾
$
的是,显示任何不匹配的内容。
意思 > 随后将显示重定向到一个文件,
我也将其添加到我的 SQL 文件中:
This is what I ended up with and works well for me:
I believe at this point, the findstr & regex are all that needs to be explained:
The regex states "At the beginning
^
, has either - or ,[-,]
any number of times*
, until you reach the end of the line$
./V
means, show me anything that doesn't match.The
>
following it redirects the display to a file.I did also add this to my SQL file:
以下行:
非常危险,因为它删除了所有包含“-”的行。
你最好看看 findstr /?并使用类似的东西:
The following line:
is very dangerous as it removes all lines containing a "-".
You better have a look at findstr /? and use something like:
如果您想将数据假脱机输出,则可以设置以下开关:
SET UNDERLINE OFF;
if you want to spool the data out then you can set the following switch:
SET UNDERLINE OFF;
我认为没有任何选项可以实现这一目标 - 恐怕您将不得不忍受这些标头。
I don't think there's any option available to achieve this - you'll have to live with those headers, I'm afraid.
使用
-h -1
选项从输出中删除列标题和破折号 (--------),并使用SET NOCOUNT ON
删除“受影响的行”在最后。如果您要创建报告或 CSV 文件以供另一个系统处理,这非常有用。示例:
在您的 SQL 脚本中:
您不需要为此在 select 语句之间使用并集。
Use the
-h -1
option to remove the column headers and the dashes (--------) from the output andSET NOCOUNT ON
to remove the "rows affected" at the end. This is great if you're creating a report or CSV file for another system to process.Example:
In your SQL script:
You don't need to use a union between your select statements for this.
我唯一能想到的是使用
-h -1
开关删除标题,并将列名添加为 SQL 查询的第一行:请注意,如果还有其他数据类型,则
(var)char
,您需要使用以下方式转换字段:CAST(MyColumnName AS varchar(32)) as MyColumnName
The only thing I can think of is removing the header using the
-h -1
switch and adding the column names as the first row to the SQL Query:Note that if there are other data types then
(var)char
, you need to convert the field using :CAST(MyColumnName AS varchar(32)) as MyColumnName
您可以在一个命令中以简单的方式完成这一切,无需任何脚本或文件操作!
添加
set nocount on;
以删除(X rows受影响)
行。添加
|findstr /v /c:"---"
以删除下划线。这样你就可以得到一个干净的答案,只有:
You can do it all in a simple way in one command, without any script or file manipulation!
Add
set nocount on;
to remove the(X rows affected)
line.Add
|findstr /v /c:"---"
to remove the underlines.This way you get a clean answer, with only:
仅使用 sqlcmd 和 Windows 命令行,不使用存储过程:
要处理内部带有分隔符的数据(例如“Atlanta, GA”),您需要单独指定字段(而不是使用“select *”)并使用SQL Server 中的 QUOTENAME 函数。
Using only sqlcmd and the Windows command line, with no stored procedures:
To handle data with delimiters inside (for example "Atlanta, GA") you'll need to specify the fields separately (rather than use "select *") and use the QUOTENAME function in SQL Server.
或者也许通过 sed as: 后处理输出
以删除第二行?
您可以从 http://gnuwin32.sourceforge.net/packages/sed.htm< 获取 Win32 sed< /a>
Or maybe post-process the output through sed as:
to delete the second line?
You can get a Win32 sed from http://gnuwin32.sourceforge.net/packages/sed.htm
为了去掉连字符,我在存储过程中添加了一些代码,这些代码将仅输出列标题。使用
SQLCMD
提取数据分为两部分。首先,我使用一组特殊的参数调用存储过程。就我而言,当我使用所有 NULL 调用 SP 时,这意味着我希望拥有列标题。
然后我再次调用
SQLCMD
并将输出附加到我之前刚刚创建的文件中,瞧!创建一个空白 CSV 文件来保存列标题
现在附加存储过程的输出结果
注意第一个命令使用>,第二个命令使用>> ;。使用时-> “创建或覆盖”和两个-> “追加或创建”。
To get rid of the hyphens, I add some code to my stored procedures that will output the column header only. Extracting data using
SQLCMD
is done in 2 parts.First I call my stored procedure with a special set of parameters. In my case, when I call the SP with all NULLs, it mean I wish to have the column header.
Then I call
SQLCMD
a second time and append the output to the file that I just created before and voila!Create a blank CSV file to hold the column header
Now append the output result from the stored procedure
Notice the first command uses > and the second one >>. When using one -> "Create or overwrite" and two -> "Append or create".
您可以执行以下操作:
这将一次性创建一个带有标题(不带破折号)并修剪了空格的 csv 文件。基于上面 MichaelM 的回答。
You can do the following:
This will create a csv file with the headers, without the dashes, and trimmed white space, in one fell swoop. Based on MichaelM's answer above.
我必须做大量的研究,因为这里没有一个答案完全符合我正在寻找的东西。所以我总结一下,希望对其他人有帮助。
对我来说,最简单的方法是使用 sed。虽然此处已建议这样做,但同时运行 sqlcmd 和 sed 的语法不正确。如果您使用的是 Windows,就像我一样,请前往 http://gnuwin32。 sourceforge.net/packages/sed.htm 下载 sed
我的最终命令如下所示:
其中
-
-m 1
消除了“将数据库上下文更改为...”行-
&&
如果第一个命令成功运行,则运行第二个命令-
sed -i 2d
删除包含连字符的第二行,并将 sed 的输出覆盖到原始输出请注意
sed
输出文件名和的输出>sqlcmd
必须匹配,否则sqlcmd
的输出将不会被正确覆盖。正如上面回答的那样,在sed
语句中的2d
周围不应有引号,并且存在于其文档中,否则您将收到错误。希望这有帮助!
I had to do a ton of research because none of the answers here exactly fit what I was looking for. So I am going to sum in hopes that it will help others.
For me, the easiest way was to use
sed
. While this was already suggested here, there was incorrect syntax to run sqlcmd and sed concurrently. If you're using Windows, as was the case for me, head to http://gnuwin32.sourceforge.net/packages/sed.htm to download sedMy final command looked something like:
Where
-
-m 1
eliminates the "Changed database context to..." line-
&&
runs the second command if the first command runs successfully-
sed -i 2d
deletes the second line containing the hyphens and overwrites the output of sed to the original outputBe aware that the
sed
output file name and the output fromsqlcmd
must match otherwise the output fromsqlcmd
will not be correctly overwritten. There should not be quotes around2d
in thesed
statement as answered above and is present in their documentation or you will get an error.Hope this helps!
如果您可以使用 PowerShell 代替
Invoke-DbaQuery
/dbatoolsInvoke-SqlCmd -Query
来自官方 MSSQL PowerShell 模块工作示例:
注意
-ReadOnly
仅对集群有帮助,并且可能有噪音(早期优化)。但我是一名查询/比较生产数据库的开发人员,因此过于谨慎,因此尽量减少 DBA 可能注意到的负载。我有时使用 https://docs.dbatools.io/#Copy-DbaDbTableData 复制整个表从 mssql 到 mssql。它很快,我认为使用 BCP.EXE 或 BulkInsert。
if you can use PowerShell instead
Invoke-DbaQuery
from https://github.com/sqlcollaborative/dbatoolsInvoke-SqlCmd -Query
from official MSSQL PowerShell moduleworking example:
Note
-ReadOnly
is only helpful on clusters, and probably noise (early optimization). But I'm a developer querying/diffing production databases, so overly cautious so minimize load which might be noticed by DBAs.I sometimes use https://docs.dbatools.io/#Copy-DbaDbTableData to copy whole tables mssql-to-mssql. It is fast, I think uses BCP.EXE or BulkInsert.
如果您可以先输出到文件(使用
-o
选项),则使用 powershell 将通过读取文件内容(同时跳过第二行)并将其写回自身来工作。If you could output to a file first (using the
-o
option), another post-process option using powershell would work by reading the file's contents--while skipping the second line--and writing it back to itself.MichaelM 有一个不错的解决方案,但正如 slingshot 指出的那样...... findstr 可能过于激进并删除包含连字符的数据行。另一种方法是调用 sqlcommand 来获取数据集,然后使用 set /p 从输出文件中获取第一行并将其分配给变量。然后删除原来的输出文件。接下来,将标头回显到新文件中。最后,将另一个无头 sqlcmd 通过管道传输到该文件。
另外,Top 0 是创建“仅标头文件”的不错选择,但前提是您使用 select 语句。如果您正在调用存储过程,请考虑使用 FMTONLY ON 仅从 SP 获取标头。
sqlcmd -S server -d database -E -Q "Set NOCOUNT ON; Set FMTONLY ON; exec myStoredProcedure" -W -o my_headers.csv -m-1
需要注意的是 FMTONLY ON 可以'不能使用 #temp 表来对抗 SP。这是因为 FMTONLY ON 不执行 SP。它只获取元数据。但是,如果列名来自执行前不存在的表,那么您将无法获取这些列名。
MichaelM has a decent solution, but as slingshot pointed out ... findstr could be over aggressive and remove data lines that contain hyphens. Another approach would be calling sqlcommand to get the data set and then using set /p to grab the first row from the output file and assign it to a variable. Then delete the orignal output file. Next, echo out the headers to a new file. Lastly, pipe another headerless sqlcmd to that file.
Also Top 0 is nice choice to create a "headers only file" but only if you are using a select statement. If you're calling a stored procedure, look into using FMTONLY ON to grab the headers only from the SP.
sqlcmd -S server -d database -E -Q "Set NOCOUNT ON; Set FMTONLY ON; exec myStoredProcedure" -W -o my_headers.csv -m-1
A caveat being that the FMTONLY ON can't be used against SP's using #temp tables. This is because FMTONLY ON doesn't execute the SP. It only grabs metadata. But if the column names are coming from tables that don't exist pre-execution then you can't get those column names.
如果输出到文件,请在成功执行后尝试以下操作:
我使用“---”,因为我的所有列都超过 3 个字符,并且我的数据中从来没有该字符串,但您也可以使用“-;-”来降低风险进一步或基于您的数据的任何分隔符来代替“;”。
If outputting to a file try the following upon successful execution:
I use "---" as all my columns are over 3 characters and I never have that string in my data but you could also use "-;-" to reduce the risk further or any delimiter based on your data in place of the ";".