Working with Bulk Copy Program Utility
page 6 of 7
by Krishnamoorthy D
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 34780/ 120

Stored Procedure to Import and Export

You need to pass three parameters, a valid file path, direction either IN or out, and a valid table name or view name.  Using these parameters, a BCP command is built as a dynamic sql as pass to extended stored procedure xp_cmdshell.

Listing 6

Create procedure dbo.IMPEXPFROMDB
@filepath   varchar(255), --filepath
@direction  varchar(5) ,  --direction(In/Out)
@tablename  varchar(255)  --Valid tablename/viewname  
as
begin
set nocount on
 
      Declare @cmd            varchar(1000)     
      Declare @dbname         varchar(1000)
 
      /* Validation for filepath */
      If (@filepath is null)
      begin
            Raiserror('Please enter the FilePath ',16,1)
            Return
      end   
      
      /* Validation for Direction  */
      If (@direction is nullor (@direction Not In('In','Out'))
      begin
            Raiserror('Please enter the Direction(In/Out)',16,1)
            Return
      end
 
 
      /* Validation for Table name */
      If (@tablename is null)
      begin
            Raiserror('Please enter the Tablename or Viewname',16,1)
            Return
      end
      else if (Object_id(@tablename)is null)
      begin
            Raiserror('Please enter a Validate Tablename or Viewname',16,1)
            Return
      end
 
 
      --Get the database name.
      set @dbname = db_name()
      
--bcp command.
set @cmd = 'bcp  '+@dbname+'..'+@tablename+' '+@direction+'  '+@filepath+' -c -S -U -P'
 
 
      --To run the bcp using extended stored procedure.
      exec master..xp_cmdshell @cmd
 
set nocount off
end

View Entire Article

User Comments

Title: Chido one   
Name: Mario
Date: 2007-04-27 1:35:29 PM
Comment:
I read many webs, but this is easy.
Tks a lot , a lot, a lot.
Title: BCP Programming   
Name: sivakumar R
Date: 2006-12-26 7:56:35 AM
Comment:
Fine , Thanks a lot
Title: Example is wrong   
Name: Faiz
Date: 2006-12-11 10:19:41 PM
Comment:
Following Example wromg .
Example : if you like to copy from differant server
also SP displaying 2 times for table name.
Thanks
Title: Bulk article   
Name: Radhakanta
Date: 2006-11-14 3:09:37 AM
Comment:
nice article and is very useful.
Title: BCP Article   
Name: Siva Kumari K
Date: 2006-09-13 4:07:27 AM
Comment:
Very Usefull Article, Really i wasted lot time before, thank u very much
Title: BCP UTILITY   
Name: BALAJI SINGH
Date: 2006-08-15 11:45:25 AM
Comment:
Bulk made easy and understandable, with this simple article.

Thanks.
Title: Now we need a BCP Format article   
Name: Darren Bates
Date: 2006-08-11 1:18:25 PM
Comment:
Thanks for the overview. What I'm trying to work with is the format file to define the data import from a text (or native) file into a table where the schema doesn't exactly match
Title: Good   
Name: Manohar
Date: 2006-08-08 2:58:19 AM
Comment:
Good
Title: BCP Article   
Name: G.Balasubramanian
Date: 2006-08-08 12:40:03 AM
Comment:
Very usefull, automates lot of process, thanks..
Title: BCP Article   
Name: Aravind Kumar
Date: 2006-08-08 12:21:20 AM
Comment:
Very useful article. This will automate lot of process and save the worktime.

Aravind
Title: Bulk article   
Name: Esmeralda Pita
Date: 2006-08-07 12:05:39 PM
Comment:
Very usufull, thanks






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-03-25 6:06:14 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search