[sql-server] sqlcmd를 사용하여 SQL Server에서 데이터를 CSV 형식으로 내보내는 방법은 무엇입니까?

PowerShell을 사용하면 Invoke-Sqlcmd를 Export-Csv로 파이프하여 문제를 깔끔하게 해결할 수 있습니다.

#Requires -Module SqlServer
Invoke-Sqlcmd -Query "SELECT * FROM DimDate;" `
              -Database AdventureWorksDW2012 `
              -Server localhost |
Export-Csv -NoTypeInformation `
           -Path "DimDate.csv" `
           -Encoding UTF8

SQL 서버 2016가 포함 SQLSERVER의 포함 모듈, Invoke-Sqlcmd당신은 단지 2016 년 그 전에 SSMS를 설치하는 경우에도 당신이해야 cmdlet을, SQL 서버 2012 이전 포함 SQLPS의 모듈 에 현재 디렉토리를 변경할 것, SQLSERVER:\모듈이 때를 다른 버그와 함께 처음 사용되었으므로 #Requires위 의 줄 을 다음과 같이 변경해야 합니다.

Push-Location $PWD
Import-Module -Name SQLPS
# dummy query to catch initial surprise directory change
Invoke-Sqlcmd -Query "SELECT 1" `
              -Database  AdventureWorksDW2012 `
              -Server localhost |Out-Null
Pop-Location
# actual Invoke-Sqlcmd |Export-Csv pipeline

SQL Server 2008 및 2008 R2의 예를 수정하려면 #Requires줄을 완전히 제거 하고 표준 PowerShell 호스트 대신 sqlps.exe 유틸리티 를 사용하십시오 .

Invoke-Sqlcmd 는 sqlcmd.exe와 동등한 PowerShell입니다. 텍스트 대신 System.Data.DataRow 개체를 출력 합니다.

이 -Query매개 -Q변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 내보낼 데이터를 설명하는 SQL 쿼리를 전달하십시오.

이 -Database매개 -d변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 내보낼 데이터가 포함 된 데이터베이스 이름을 전달하십시오.

이 -Server매개 -S변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 내보낼 데이터가 포함 된 서버 이름을 전달하십시오.

Export-CSV 는 일반 개체를 CSV로 직렬화하는 PowerShell cmdlet입니다. PowerShell과 함께 제공됩니다.

이 -NoTypeInformation매개 변수는 CSV 형식의 일부가 아닌 추가 출력을 억제합니다. 기본적으로 cmdlet은 형식 정보가 포함 된 헤더를 작성합니다. 나중에와 함께 직렬화 해제 할 때 객체의 유형을 알 수 Import-Csv있지만 표준 CSV가 필요한 도구를 혼동합니다.

이 -Path매개 -o변수는 sqlcmd.exe 의 매개 변수 와 같이 작동합니다 . 이전 SQLPS 모듈을 사용하는 경우이 값의 전체 경로가 가장 안전합니다 .

-Encoding매개 변수는 같이 작동 -f또는 -usqlcmd.exe를의 매개 변수를 설정합니다. 기본적으로 Export-Csv는 ASCII 문자 만 출력하고 다른 모든 문자는 물음표로 바꿉니다. 대신 모든 문자를 유지하고 대부분의 다른 도구와 호환되도록 UTF8을 사용하십시오.

sqlcmd.exe 또는 bcp.exe보다이 솔루션의 주요 장점은 유효한 CSV를 출력하기 위해 명령을 해킹 할 필요가 없다는 것입니다. Export-Csv cmdlet은이를 모두 처리합니다.

주요 단점은 Invoke-Sqlcmd파이프 라인을 따라 전달하기 전에 전체 결과 세트 를 읽는다는 것입니다. 내보내려는 전체 결과 세트에 충분한 메모리가 있는지 확인하십시오.

수십억 행에 대해 원활하게 작동하지 않을 수 있습니다. 문제가있는 경우 다른 도구를 사용해 보거나 System.Data.SqlClient.SqlDataReader 클래스 를 Invoke-Sqlcmd사용하여 효율적인 버전을 롤링 할 수 있습니다 .

답변

sqlcmd -S myServer -d myDB -E -o "MyData.txt" ^
    -Q "select bar from foo" ^
    -W -w 999 -s","

bcp "select col1, col2, col3 from database.schema.SomeTable" queryout  "c:\MyData.txt"  -c -t"," -r"\n" -S ServerName -T

bcp /?

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]

bcp.exe MyTable out "D:\data.csv" -T -c -C 65001 -t , ...

sqlcmd -S servername -U username -P password -d database -Q "set nocount on; set ansi_warnings off; sql query here;" -o output.tmp -s "," -W
type output.tmp | findstr /V \-\,\- > output.csv
del output.tmp

$chunk_size = 10000
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT * FROM <TABLENAME>"
$command.Connection = $connection
$connection.open()
$reader = $command.ExecuteReader()

$read = $TRUE
while($read){
    $counter=0
    $DataTable = New-Object System.Data.DataTable
    $first=$TRUE;
    try {
        while($read = $reader.Read()){

            $count = $reader.FieldCount
            if ($first){
                for($i=0; $i -lt $count; $i++){
                    $col = New-Object System.Data.DataColumn $reader.GetName($i)
                    $DataTable.Columns.Add($col)
                }
                $first=$FALSE;
            }

            # Better way to do this?
            $data=@()
            $emptyObj = New-Object System.Object
            for($i=1; $i -le $count; $i++){
                $data +=  $emptyObj
            }

            $reader.GetValues($data) | out-null
            $DataRow = $DataTable.NewRow()
            $DataRow.ItemArray = $data
            $DataTable.Rows.Add($DataRow)
            $counter += 1
            if ($counter -eq $chunk_size){
                break
            }
        }
        $DataTable | Export-Csv "output.csv" -NoTypeInformation -Append
    }catch{
        $ErrorMessage = $_.Exception.Message
        Write-Output $ErrorMessage
        $read=$FALSE
        $connection.Close()
        exit
    }
}
$connection.close()

exec master..xp_cmdshell 'BCP "sp_who" QUERYOUT C:\av\sp_who.txt -S MC0XENTC -T -c '