Export data in SQL Server using BCP command

 

The BCP command in SQL Server is a command-line utility that bulk copies data between a SQL Server instance and a data file in a user-specified format. It is used to import large amounts of data from a text file into a SQL Server table or to export data out of a table into a text file. It is also used to create format files that store metadata about the data, such as field types and lengths.

 

Procedure is to export data from SQL Server data table into csv with pipe "|" delimiter.

 

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: EXECUTE Procedure to build BCP Command against any table in database

EXECUTE sqlcache_GenerateTableBCPCommand 'DBName','TableName','DestinationFileName'

 

--- Script By: Amna Asif___________________For: http://sqlcache.blogspot.com  
--- Purpose: Create BCP Command against any table in database

USE DBName
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sqlcache_GenerateTableBCPCommand]
@DBName varchar(255),
@TABLE VARCHAR(255),
@FILE VARCHAR(255)
AS

CREATE TABLE #QUERY (  QUERY VARCHAR(MAX)  )

DECLARE @SERVERNAME VARCHAR (255);
SET @SERVERNAME='SqlCache'

------------------------------Generate comma seprated columns of target table
declare @cmd varchar(4000), @sql varchar(8000)
SELECT @sql =''
INSERT INTO #QUERY
SELECT DISTINCT
' SELECT '+(STUFF((
                    SELECT ', ' +  ''''''+name+ ''''''
                    FROM SYS.COLUMNS Z
                    WHERE Z.object_id=Y.object_id
                    AND Y.object_id=OBJECT_ID(C.TABLENAME)
                    FOR XML PATH('')
                    ), 1, 2, ''))  
+' UNION ALL SELECT ' + (STUFF((
                                SELECT ', ' + 'cast(['+name+'] as varchar(500))'
                                FROM SYS.COLUMNS Z
                                WHERE Z.object_id=Y.object_id
                                AND Y.object_id=OBJECT_ID(C.TABLENAME)
                                FOR XML PATH('')
                                ), 1, 2, ''))+' FROM '+@DBName+'.dbo.'+TABLENAME   AS S
FROM (
    SELECT [Tables].name AS [TableName],
    SUM([Partitions].[rows]) AS [TotalRowCount]
    FROM sys.tables AS [Tables]
    JOIN sys.partitions AS [Partitions]
    ON [Tables].[object_id] = [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
    WHERE [Tables].name like '%'+@TABLE+'%'
    GROUP BY SCHEMA_NAME(schema_id), [Tables].name) AS C
INNER JOIN SYS.COLUMNS Y ON Y.object_id=OBJECT_ID(C.TABLENAME)
--WHERE TotalRowCount >0

--------------------------------Generate BCP command and exports data from user table into pipe delimited csv file
 SELECT @sql= ( SELECT TOP 1  QUERY FROM #QUERY )

SET @Cmd = 'master..xp_cmdshell ''bcp "' + @sql + '" queryout D:\DestinationFolder\'+@SERVERNAME+'_'+@FILE+'.csv -c -t^| -S'+@@SERVERNAME+' -T -Usqlcacheuser -Psqlcachepass'''

EXEC (@cmd)

GO