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