Monday, July 29, 2013

BCP on SQL server and how to export metadata like table headers

A known limitation using "bcp" utility in SQL Server (2k5,2k8,2012) is that there is no way to include meta-data (say header names) to any exported table. There are some trick you can do to overcome such problem.
The basic idea is to create two ascii files, one with the header and one with the data. Then, you can create the final file that is the result of the concatenation of the above. Note the trick that is used in order to get a string that contains the columns of the respected table with the use of the global temp table.
I hope that this solution (that is indeed not very elegant) will help any friend facing the same problem.



--Definitions
declare @Cmd as varchar(2048)
declare @filename1 as varchar(255)
declare @filename2 as varchar(255)
declare @filename3 as varchar(255)
declare @path as varchar(255)
 
set @path='C:\work\exported\'
 
-- create the 1st temp ascii file with column names (metadata)
select @filename1 = cast(newid() as varchar(36)) + '.txt'
declare @colnames varchar(2000)
SELECT @colnames = COALESCE(@colnames  + '||', '') + column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tbl_parts';
 
-- Store the variable with metadata in global temp table in order to be used later by bcp.   
select @colnames as cname into ##ColumnsTable
 
-- Export metadata from global temp, using bcp, to a temp ascii file.
set @Cmd = 'bcp "SELECT * FROM ##ColumnsTable" queryout "'+@path+@filename1+'" -T -S".\SQLEXPRESS" -c'   
select @cmd
EXEC xp_cmdshell @Cmd
 
-- I don't need anymore the global temp.
drop table ##ColumnsTable
 
-- create the 2nd temp ascii file with the actual data
set @filename2 = cast(newid() as varchar(36)) + '.txt'
set @Cmd = 'bcp "SELECT * FROM [TestDB].[dbo].[tbl_parts]" queryout "'+@path+@filename2+'" -T -S".\SQLEXPRESS" -c -t"||"'
EXEC xp_cmdshell @Cmd
 
-- Concatenate both temp ascii files to the final file and remove the temp/s ----------------
-- The name of the final temp will be ExportedFile_YYYYMMDD.txt where YYYY= current year, MM=current month and DD=current day.
-- Note that any file you export **at the same day**, will overwite all others.
set @filename3 = 'ExportedFile_' +replace(CONVERT(VARCHAR(10), getdate(), 126),'-','') + '.txt'
set @Cmd = 'copy /b "'+@path+@filename1+'"+"'+@path+@filename2+'" "'+@path+@filename3+'"'
select @Cmd
EXEC xp_cmdshell @Cmd
  
set @Cmd = 'del "'+@path+@filename1+'"'
select @Cmd
EXEC xp_cmdshell @Cmd
 
set @Cmd = 'del "'+@path+@filename2+'"'
select @Cmd
EXEC xp_cmdshell @Cmd

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.