代码之家 › 专栏 › 技术社区 › Karl

SQL Server 2008-使用cmd将标题输出到.csv

sqlcmd cmd batch-file csv sql-server-2008

Karl · 技术社区 · 15 年前

我有一个非常简单的问题(这些问题通常是我大部分时间都在扯头发的问题)。我使用批处理文件来执行与批处理位于同一目录中的所有.sql查询,并将它们的所有结果保存到不同的.csv文件中。

这是我的密码:

@echo off

REM Check that all parameters are present
@IF "%1"=="" goto error
@IF "%2"=="" goto error
@IF "%3"=="" goto error
@IF "%4"=="" goto error

REM For every *.sql file in this folder "." and all its subfolders (/R), do the following: 
@for /R "." %%F in (*.sql) do (
REM Print the command to be executed:
@echo Database : @SqlCmd -S %1 -U %2 -P %3 -d %4 -I -l60 -i "%%F" -o "%%F.output.csv"  -h-1 -s"," -w 1000 -W
REM Execute the command:
@SqlCmd -S %1 -U %2 -P %3 -d %4 -I -l60 -i "%%F" -o "%%F.output.csv" -h-1 -s"," -w 1000 -W
)

goto :EOF

:error

@echo Incorrect syntax : 
@echo     extract.cmd [servername] [user id] [password] [databasename]
@echo   
@pause

goto :EOF

作为测试,我运行以下查询:

select top(10) [name] from sysobjects

输出:

sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
sysfgfrag
sysphfg
sysprufiles
sysftinds
sysowners

(10 rows affected)

这很管用,除了两件事。首先,我还需要输出列标题。所以我去掉“-h-1”参数,它会给出:

name
----
sysrscols
sysrowsets
sysallocunits
sysfiles1
syspriorities
sysfgfrag
sysphfg
sysprufiles
sysftinds
sysowners

(10 rows affected)

这很好,只是我不想在标题和第一行数据之间使用水平规则“-----”。

另外,我不想输出“(10行受影响)”行。

谢谢

3 回复 | 直到 15 年前

KM. 15 年前

试试这个:

SET NOCOUNT ON;
SELECT
    [Name]
    FROM (SELECT TOP(10)
              2 AS SortBy, [name]
              FROM sysobjects 
          UNION
          SELECT 1, 'Name'
         ) dt
    ORDER BY [Name]

SSMS输出:

Name
----------------------------------------------
Name
sysallocunits
sysfiles1
sysftinds
syshobtcolumns
syshobts
sysowners
sysprivs
sysrowsetcolumns
sysrowsets
sysserefs

保留“-h-1”参数,列标题将被删除,但“Name”行仍将首先出现在结果集中。

juur 15 年前

据我所知,在使用sqlcmd时不能去掉“---”行。

PollusB 13 年前

CSV.CMD文件“从主..系统数据库中选择*”

@echo off
REM Execute a SQL command or a SQL file on multiple instances.
REM Pollus Brodeur January 2007

if not x==%1x goto MAIN
echo Error  : You need to pass a query in parameter
echo Exemple: C:\>csv.bat "select * from table"
goto END

:MAIN

echo ===============================================================================
echo CSV) Execute a TSQL command on multiple servers and consolidate the result
echo ===============================================================================

set QUERY=%1

echo For the following query :
echo %QUERY%

:EXEC
echo ===============================================================================
echo On the followinf instances:
type servers.lst
echo ===============================================================================
echo To modify list : Ctrl+C notepad c:\script\servers.lst
echo ===============================================================================
PAUSE

echo Executing...
if NOT EXIST .\out mkdir out
if EXIST out\output.csv (del out\output.csv)
FOR /F %%s in (servers.lst) DO echo %%s & bcp %QUERY% queryout out\csv.%%s.out -S %%s -T -c -t;

:ENDEXEC
type out\csv.*.out >> out\output.csv
echo ===============================================================================
echo Execution finished
echo To view results with MORE      : more /E /C out\csv.*.out.*.txt
echo ===============================================================================

:END
DEL out\csv.*.out
PAUSE
out\output.csv

这只适用于Windows身份验证,但您可以根据需要更改代码。