A stored procedure to export/import data using BCP

This is a stored procedure that can be run from a master database to export or import a table to/from a given file...

path+name from/to a given database.

  
create Proc Sp_Export_Or_import_Table @dbName     varchar(30), 
                                      @tbName     varchar(30), 
                                      @filePath   varchar(80), 
                                      @cmode      char(6), 
                                      @sep        char(1), 
                                      @usr        varchar(30), 
                                      @pwd        varchar(30) 
as 
declare @cmd varchar(200) 
begin 
  IF @cmode = 'EXPORT' 
  begin 
      set @cmd = 'bcp.exe ' +  
                 @dbName + '..' + @tbName +  ' out '  + 
                 @filePath + ' -c -q -C1252 -U ' + @usr + 
                 ' -P ' + @pwd + ' -t' + @sep 
      print @cmd + '...' 
      exec xp_cmdShell @cmd 
  end 
  IF  @cmode = 'IMPORT' 
  begin 
     set @cmd = 'bcp.exe ' +  
                 @dbName + '..' + @tbName +  ' in '  + 
                 @filePath + ' -c -q -C1252 -U ' + @usr + 
                 ' -P ' + @pwd + ' -t' + @sep 
      print @cmd + '...' 
     exec xp_cmdShell @cmd   
  end 
End 
go 
-- *********************************************************** 
-- How To Use it 
-- Export a Table From a given DataBase to a File. 
Exec Sp_Export_Or_import_Table 'Northwind', 
                               'Orders', 
                               'c:tempdbNorthwind_Orders.Dat', 
                               'EXPORT', 
                               '@', 
                               'iecdba', 
                               'sapwd' 
-- Import a table to a given DataBase from a File 
use pubs 
go 
select * into newOrders from northwind..orders where 0 = 1 
go 
use master 
go 
Exec Sp_Export_Or_import_Table  'pubs', 
                               'NewOrders', 
                               'c:tempdbNorthwind_Orders.Dat', 
                               'IMPORT', 
                               '@', 
                               'iecdba', 
                               'sapwd' 
-- *********************************************************** 

Reader Feedback

Kumar S. writes: This is really a good stuff and came at the right time -- I was looking for a similar job in my project. But do you have any idea how to handle errors in case of bcp failures or errors in bcp?

Rajasekhar S. writes: Could you please tell me whether this utility works with image and text data types?

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.