IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_binaryio]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_binaryio] Go
/ * - BCP implementation of the import of binary files
Support for image, text, nText fields for import / export images for binary, including: Word documents, Excel documents, pictures, music, etc. TEXT, NTEXT is suitable for text data files
Note: Import does not add a record, so before importing the table, when the recording import is inserted, all rows that cover the condition will be overwritten, and all the rows that satisfy the condition will be exported to the specified file.
This stored procedure is implemented in only BCP
- Zou Jian 2003.08 (Please keep this information) --- * /
/ * - Call example - Data Export EXEC P_BINARYIO 'ZJ', '', '', 'ACC_ Data ..TB', 'IMG', 'C: /ZJ1.DAT'
- Data Import EXEC P_BINARYIO 'ZJ', '', '', 'ACC_ Presented Data ..TB', 'IMG', 'C: /ZJ1.DAT', '', 0 - * / CREATE PROC P_BINARYIO @servename varchar (30), - server name @uSername varchar (30), - User name @password varchar (30), - password @tbname varchar (500), - Database .. Name @fdname varchar ( 30), - Field Name @fname varchar (1000), - Directory file name, use / override during processing: @ filename _TEMP @ TJ varchar (1000) = '', - handling conditions. For data Import, if the condition contains @fdname, specify the table name prefix @isout bit = 1 --1 export ((default), 0 import as declare @fname_in varchar (1000) --BCP processing answer file name, @ fsize varchar ( 20) - The size of the file to be processed, @ m_tbname varchar (50) - Temporary table name, @ SQL varchar (8000)
- The size of the import file is obtained if @ iSout = 1 set @ fsize = '0'lsebegin create table #TB (optional VARCHAR (20), size int, created date varchar (10), create time varchar (20) , Last written operation date Varchar (10), last written operation time varchar (20), last visited date varchar (10), last visited VARCHAR (20), Features INTO #TB EXEC MASTER.. Xp_GetfileDetails @fname select @ fsize = size from #tb drop table #tb if @fsize is null begin print 'Did not find' return end
end
- Generate data processing answer file set @m_tbname = '[## Temp' Cast (newid () as varchar (40)) ']' set @ SQL = 'SELECT * INTO' @ m_tbname 'from (SELECT NULL AS Type UNION All SELECT 0 AS Prefix Union All SELECT ' @ fsize ' AS Length Union All Select Null AS End Union All Select Null AS Formats) A'EXEC (@SQL) SELECT @ fname_in = @ fname '_ Temp', @ SQL = 'bcp "' @ m_tbname '" out "' @ fname_in '" / s "' @ servename case when isnull (@username, '') = '' TEN '' ELSE '" / u "' @ username end '"/ p"' isnull (@password, '') '"/ c'exec master..xp_cmdshell @ SQL - Delete Temporary table set @ SQL =' Drop Table ' @ m_tbnameexec @SQL) IF @ isout = 1begin set @ SQL = 'bcp "SELECT TOP 1' @ fdname 'from' @ TBNAME CASE ISNULL (@tj, '') when '' Ten 'Else' Where ' @ Tj end '"queryout"' @ fname '"/ s" @ servename case when isnull (@username,' ') =' 'TEN' ELSE '"/ u"' @ username end '"/ P"' isnull (@password, '') '"/ i" "'" "'" "" ""' "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" 0 ' @ fdname ' INTO ' @ m_tbname ' from ' @ TBNAME EXEC (@SQL)
- Import data to temporary table set @ SQL = 'bcp "' @ m_tbname '" in "' @fname '" / s "@ Servename Case When Inull (@username,' ') =' 'TEN' 'ELSE' "/ u" ' @ username end ' "/ p" ' isnull (@password,' ') ' "/ i" ' @ fname_in ' "'exec master..xp_cmdshell @ SQL - Import Data into the official table set @ SQL = 'update' @ TBNAME 'set' @ fdname '= B.' @fdname 'from' @ TBNAME 'A,' @ m_tbname ' B ' Case Isnull (@tj,' ') when' '' '' else 'Where' @ Tj end exec (@SQL) - Delete Data Processing Temporary Table Set @ SQL = 'Drop Table' @ m_tbnameeend
- Delete data processing answer file set @ SQL = 'del' @ fname_INEXEC MASTER..XP_CMDSHELL @SQLGO
