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

 

 

Import and Export Data from SQL Server

To use OPENROWSET you have to enable Ad Hoc Distributed Queries

 sp_configure 'show advanced options', 1;
 RECONFIGURE;
 GO
 sp_configure 'Ad Hoc Distributed Queries', 1;
 RECONFIGURE;
 GO

Excel
1. To Import Data from Microsoft Office Excel 2007 Worksheet(.xlsx)
Step 1: Configure ACE Driver

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
  GO
  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
  GO

Step 2:

SELECT * INTO dbo.ExcelWorksheetContacts
  FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
       'Excel 12.0;HDR=NO;Database=E:\SQLCAT\book1.xlsx;',
       [Contacts$]
               )

2. To Import Data from Microsoft Office Excel 97-2003 Worksheet(.xls)

 SELECT * INTO dbo.ExcelWorksheetContacts
 FROM
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',
            'Excel 8.0;HDR=YES;Database=E:\SQLCAT\ExcelWorksheet.xls',
            'Select FirstName,LastName,DateofBirth,Address1,City,State,ZipCode From [Contacts$]')   

3. To Import Data from Excel with Restricted Columns and Rows

 SELECT * INTO dbo.ExcelWorksheetContacts
 FROM
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',
               'Excel 8.0;Database=E:\SQLCAT\ExcelWorksheet.xls;HDR=yes',
               'SELECT * FROM [Contacts$A1:C3]'
               )
   

4. To Export Data in Excel (Already Have format) from SQL Table  
     1. Excel Sheet must contain Header in First Row
     2. No of Columns must match  

    INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                           'Excel 8.0;Database=E:\SQLCAT\ExcelWorksheet.xls;HDR=yes',
                           'SELECT * FROM [Contacts$]') SELECT * FROM  dbo.ExcelWorksheetContacts  
      

5. To Import Data from Excel using BCP command
     1. Table ExcelWorksheetContacts should already exist

 DECLARE @sql VARCHAR(500)
 SELECT @sql= 'bcp "SQLCat.dbo.ExcelWorksheetContacts" in "E:\SQLCAT\ExcelWorksheet.xls" -S -PC\SQLSERVER2008R2 -T -c'
 EXEC xp_cmdshell @sql                
 

6. To Export Data in Excel from SQL Table using BCP 
     1. Excel File should already exist

    EXEC xp_cmdshell 'bcp "Select * from SQLCat.dbo.ExcelWorksheetContacts" queryout "E:\SQLCAT\ExcelWorksheet.xls" -S -PC\SQLSERVER2008R2 -T -c' 
  

SQL Server Error:                          
IF you found following ERROR during Import through TSQL just restart the SQL Server Services
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Text File 
1. To Import from a Text File to SQL Table
    1. Table Text_ConstactsFile should already exist

 DECLARE @sql VARCHAR(500)
 SELECT @sql= 'bcp "SQLCat.dbo.Text_ConstactsFile" in "E:\SQLCAT\Text_ConstactsFile.txt" -S -PC\SQLSERVER2008R2 -T -c'
 EXEC xp_cmdshell @sql

2. To Export in Text File from SQL Table
    1. Text file should already exist

 EXEC xp_cmdshell 'bcp "SELECT * FROM SQLCat.dbo.Text_ConstactsFile" queryout "E:\SQLCAT\Text_ConstactsFile.txt" -S -PC\SQLSERVER2008R2 -T -c'

3. Bulk Insert from Text file to SQL Table
    1. Table Text_ConstactsFile should already exist

 BULK INSERT SQLCat.dbo.Text_ConstactsFile
    FROM 'E:\SQLCAT\Text_ConstactsFile.txt'
    WITH
    (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
    )

CSV File 
1. To Import Data From CSV File to SQL Table

  SELECT * INTO SQLCat..CSV_ContactsSheet
  FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=E:\SQLCAT\;HDR=yes;'
                  ,'SELECT top 2 * FROM CSV_ContactsSheet.CSV'
                  )

and an excellent link on Excel Import\Export
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.html