Export Table to CSV file with Column Header (Name) using bcp utility in SQL Server

Last Reply 4 months ago By dharmendr

Posted 4 months ago

Hi

I have in error in Mssql Store procedure using BCP bulk transfer data into csv file Error : - "Must declare the scalar variable "@EXPORT_EXCEL"

Pls advice me.

This is Strored Procedure

SET NOCOUNT ON;
DECLARE @Servername varchar(100)
SET @Servername ='xxxxxx'
 
DECLARE @EXPORT_EXCEL TABLE
(
    [BL_NO] [varchar](30) NULL,
    [VESSAL_NAME] [varchar](100) NULL,
    [CONTAINER_NO] [varchar](50) NULL,
    [CONTAINER_TYPE] [varchar](10) NULL
 
)     
 
INSERT INTO @EXPORT_EXCEL (BL_NO, VESSAL_NAME,CONTAINER_NO,CONTAINER_TYPE)
SELECT BL_NO, VESSAL_NAME,CONTAINER_NO,CONTAINER_TYPE FROM [dbo].[ZR_tbl_Export_FILE] 
ORDER BY VESSAL_NAME

declare @sql varchar(8000)
select @sql = 'bcp guard_security.dbo.' + @EXPORT_EXCEL + 'out c:\ExportExcel\Export_FILE.csv -c -t, -T -S' +  @servername
exec master..xp_cmdshell @sql

Problem:  Header are not displyed in Excel / CSV

So i have insert Heder also into virual table then I export.

that why, i am using virtul table

Posted 4 months ago Modified on 4 months ago

Hi maideen,

Check this query.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

DECLARE @Servername varchar(100)
SET @Servername = @@servername
DECLARE @columnHeader VARCHAR(8000)
SELECT @columnHeader = '''CustomerID'',''ContactName'',''City'',''Country'''
 
declare @sql varchar(8000)
select @sql = 'bcp "SELECT '+ @columnHeader +' UNION ALL SELECT TOP 5 CustomerID,ContactName,City,Country FROM Northwind..Customers" queryout D:\Export_FILE.csv -c -t, -T -S' + @servername
exec master..xp_cmdshell @sql

Screenshot

Query output and Exported CSV