BCP in a table

,

This script works with the dbsp_bcp_out procedure to import data using BCP.

if object_Id( 'dbsp_bcp_in') Is Not Null
 drop procedure dbsp_bcp_in
go

CREATE procedure dbsp_bcp_in
	@tbl varchar( 40) = NULL,
	@db varchar( 30) = NULL,
	@path varchar( 200) = 'c:\'
as
/*
*************************************************************
Name: dbsp_bcp_in
Description:
   BCPs in an entire table from a local file on the server. The
user passes in the name of a table or view, the database in which
this is located and (optionally) the path where the text file is
located. The file must by in the format: <table>.txt

Usage: exec dbsp_bcp_in <tbl>, <db>, <path>
	exec dbsp_bcp_in 'member', 'iqd_dev', 'c:\mssql'

Author: Steve Jones (7-26-1999)
Copyright: 1999 dkRanch.net

Input Params:
-------------
@tbl		Name of a table or view to bcp out of the server
@db		Name of the database in which the table is located.
@path		path in which to write output file.

Output Params:
--------------

Return: 0 if no error.

Results:
---------

Calls: master..xp_cmdshell

Locals:
--------
@err		Holds error value
@cmd          Holds BCP command 

Modifications:
--------------

*************************************************************
*/
set nocount on
declare @err int,
	 @cmd varchar( 250)

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @tbl Is NULL
 select @err = -1
if @db Is NULL
 select @err = -1
if @err = -1
 begin
  Raiserror( 'Parameter Error:Usage:exec dbsp_bcp_in <tbl>, <path>', 12, 1)
  return @err
 end
if ( select count(*) from sysobjects where name = @tbl) = 0  
 begin   
  Raiserror( 'Table does not exist;Please pass in the name of an existing table.', 12, 1)
  return  -1
 end  
if ( select count(*) from master..sysdatabases where name = @db) = 0  
 begin   
  Raiserror( 'Database does not exist;Please pass in the name of an existing database.', 12, 1)
  return  -1
 end  


/*    Ensure there is a backslash on the path */
if right( rtrim( @path), 1) != '\'
  select @path = rtrim( @path) + '\'  


/*
Build the BCP command string
*/
select @cmd = 'bcp ' + rtrim( @db) + '..' + rtrim( @tbl) + ' in ' + rtrim( @path) + rtrim( @tbl) + '.txt -n -S' + @@servername + ' -U sa -T -E'  
select @cmd = 'master..xp_cmdshell ''' + rtrim( @cmd) + ''''

/*
Run the BCP command to move the data out.
*/
exec( @cmd)  

return @err
GO
if object_id( 'dbsp_bcp_in') Is Null
 select 'dbsp_bcp_in Not Created'
else
 select 'dbsp_bcp_in Created'
go

Rate

4.6 (5)

Share

Share

Rate

4.6 (5)