Create Procedure sp_DBABCPIn  	@db varchar(10),		-- Database Name
		  		@srv varchar(10),		-- Server Name
			 	@path varchar(10) = 'C:\BCP\'	-- Import Directory
					
AS	
	      
/**********************************************************************************************************
  * Procedure:  sp_DBABCPIn
  *    
  *   Purpose:  BCP's data from a specified location into a specified database on  
  *  		 a given server 
  *
  *    Inputs:   Database Name, Server Name, Output Path (Optional)
  *   Assumes:  There exists a directory called  C:\BCP, if no output directory is given
  *
  *    Output:   3 files per table :- <database>_<table>.dat  ( BCP'ed output Data file )
  *				      <database>_<table>.out  ( BCP Statical info )
  *				      <database>_<table>.err  ( Error File )
  * --------------------------------------------------------------------------------------------
  */

declare @tbl varchar(255), 
	@next_tbl varchar(255),
	@cmd Varchar(1000)




exec ('declare d cursor for SELECT name FROM ' + @db + '..sysobjects WHERE type = ' + '''u''')

open d
fetch next from d into @tbl
SET NOCOUNT  ON


while (@@FETCH_STATUS<>-1) begin
	
	select @cmd = 	'bcp  '+ @db+ '..' + @tbl + ' in '  +  @path + @db +'_'+ @tbl +'.dat'  +
			' /e ' + @path + @db +'_'+ @tbl +'.err'  + ' /n /E /o ' + @path + @db +'_'+ @tbl +'.out'  +
			' /T  /S ' + @srv

	execute master..xp_cmdshell @cmd 
	Print 'BCP the ' + @tbl + ' Table'

	fetch next from d into @tbl
	
End
close d
deallocate d