A stored procedure to export/import data using BCP
This is a SP that can be run from a master database to export/import a table to/from a given path+name from/to a given DB.
This is a stored procedure that can be run from a master database to export or import a table to/from a given file...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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 [email protected] 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.