BCP is used in SQL server either exports data to external files or imports data from external files (.txt, .csv etc). You can execute the bcp utility form command prompt (cmd) and T-SQL, further you can schedule the job to do the bcp task on regular basis, if required.
See an example of BCP using T-SQL:
declare @sqlI2 varchar(8000)
declare @pathI2 varchar(100)
SET @pathI2='C:\Data'+Convert(Varchar(50),GetDate(),105) +'.csv'
select @sqlI2 = 'bcp "SELECT ''Id'' AS ID , ''Name'' AS Name,''Date'' AS Date UNION ALL SELECT CONVERT(Varchar(10),Id) AS ID, Name,CONVERT(Varchar(50),GetDate()) AS Date FROM TEST..Employee" queryout '+@pathI2+' -c -t, -U sa -P sa -S AKSSRV'
exec master..xp_cmdshell @sqlI2
Just set the SQL statement, location of output path, SQL server name, user id and password and execute the code. The output of file is copied to the specified location.
See an example of BCP using command prompt:
Open the command prompt (run -> cmd) and paste the following code:
bcp "SELECT CONVERT(Varchar(10),Id) AS ID,Name,GetDate() AS Date FROM TEST..Employee" queryout C:\Employee.txt -c -
Change the required parameters and press enter the code will be executed.
List of bcp utility Parameters:
Bcp –h command is used to show the list of BCP parameter
C:\ >bcp -h
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"]
More info: http://msdn.microsoft.com/en-us/library/ms162802.aspx
Comments