bcp és sqlcmd alapok

Két hasznos konzol eszköz az sql szerver használók részére.

bcp  (Bulk Copy Program)

Elérése:
"c:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe" 
Parancs soros eszköz mely a forrás adatok adatbázisba töltésére és adatok állományba töltésére szolgál

Jellemzői:
- parancssori program
- korlátozott adatkonverzió és ellenőrzés
- Sükséges jog: SELECT, INSERT, ALTER

Használat:
bcp db.tabla in | out textfile

bcp "SELECT * FROM dbo.tabla" queryout Test.csv -t, -c -S . -d Demo50 -T

bcp "select * from db.dbo.Table" queryout c:\tmp\demo.xls -c -k -SServerName -U sa -P 111 -w-t mezőterminátor
-r rekordterminátor
-F átugrott első rekordok darabszáma

SQLCMD

Elérése:

"c:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
Parancs soros eszköz SQL utasítások futtatására

Példák:
sqlcmd -U felhasználó -P jelszó -e -i createTables.sql

sqlcmd -U dbadmin -P jelszó -e -i createDatabase.sql

sqlcmd -U usernev -P jelszo -Q"SELECT column1, column2 FROM DATA_TABLE" -oOutput_file

sqlcmd -U felh_nev -P jelszo -S „szerver neve”\”sql server példány neve” -d „Adatbázis neve” -Q "EXEC dbo.sp_eljaras @Tipus=1, @BOType=2"

SQLCMD -S servername -E -d adatbazisnev -Q "set nocount on;SELECT * FROM tablanev" -s "|" -o "D:\tmp\proba.csv" -h -1 -W

SQLCMD -S SQLServer -d Database -U UserName -P Password -Q “SQL Query” -s “,” -o “C:\name.csv”


Help:
Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f  | i:[,o:]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

BULK INSERT

 Speciális SQL parancs
− csak adatbetöltésre (bcp funkcionális)
− igényelt jog: INSERT, ALTER
− a LOGIN OS joggal olvassa be a fájlt


Használata:
BULK INSERT db.tabla FROM file WITH FIRSTROW = n 
ROWTERMINATOR=… FIELDTERMINATOR=…

sqlps.exe 

PowerShell mini shell

SQLCMD-szerű lekérdezés (Invoke-SqlCmd)

$filepath = 'directory_pathmy_excel_file.xls'

#Connection String for Excel 2007:

$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"

#Connection String for Excel 2003:

#$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"

$qry = 'select * from [Sheet1$]'

$sqlserver = 'my_server_name'

$dbname = 'my_target_database'

#Create a table in destination database with the with referenced columns and tablename.

$tblname = 'temp_PATALGCD'

#######################

function Get-ExcelData

{

    param($connString, $qry='select * from [sheet1$]')

    $conn = new-object System.Data.OleDb.OleDbConnection($connString)

    $conn.open()

    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)

    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)

    $dt = new-object System.Data.dataTable

    [void]$da.fill($dt)

    $conn.close()

    $dt

} #Get-ExcelData

#######################

function Write-DataTableToDatabase

{

    param($dt,$destServer,$destDb,$destTbl)

    $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"

    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

    $bulkCopy.DestinationTableName = "$destTbl"

    $bulkCopy.WriteToServer($dt)

}# Write-DataTableToDatabase

#######################

$dt = Get-ExcelData $connString $qry

Write-DataTableToDatabase $dt $sqlserver $dbname $tblname