AWG Blogs

Saturday, November 30, 2013

Create empty file with bcp

I wasn't sure if multiple statements (using semicolon as delimiter) could be passed to bcp, so I tried it, specifically prepending a DECLARE statement and it worked, in this case for creating an empty file:
DECLARE @FilePath VARCHAR(1000)
DECLARE @FileExists int
DECLARE @nulltable TABLE ( foo integer)
SET @FilePath = 'C:\my\path\somefile.csv'

exec master.dbo.xp_fileexist @FilePath, @FileExists OUTPUT
if not @FileExists = 1 
begin
declare @bcpCommand varchar(255), @Result int
set @bcpCommand = 'bcp "DECLARE @nulltable TABLE ( foo integer); Select * from @nulltable" queryout "' + @FilePath + '" -c -t, -T -S '
exec @Result = master..xp_cmdshell @bcpCommand  --, no_output
end