SQL Binary File Import and Export

* -- Bcp-Import and Export of binary files supports image, text, and ntext fields. image is suitable for binary files. text and ntext are suitable for text data files. Note: during import, when all rows that meet the conditions are overwritten for export, all rows that meet the conditions are also exported to the specified file. This stored procedure only uses bcp to implement limit 2003.08 ----------

/* -- Bcp-Import and Export of binary files supports image, text, and ntext fields. image is suitable for binary files. text and ntext are suitable for text data files. Note: during import, when all rows that meet the conditions are overwritten for export, all rows that meet the conditions are also exported to the specified file. This stored procedure only uses bcp to implement limit 2003.08 ----------

/* -- Bcp-Import and Export of binary files

Supports import/export of image, text, and ntext Fields

Image is suitable for binary files; text and ntext are suitable for text data files.

Note: during import, all rows meeting the conditions will be overwritten.

During export, all rows that meet the conditions will also be exported to the specified file.

This stored procedure is only implemented using bcp

--2003.08 -----------------*/

/* -- Call example

-- Data export

Exec p_binaryIO 'zj', '','', 'Acc _ demo data .. tb', 'img ', 'c: zj1.dat'

-- Data export

Exec p_binaryIO 'zj', '','', 'Acc _ demo data .. tb', 'img ', 'c: zj1.dat', '', 0

--*/

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

Create proc p_binaryIO

@ Servename varchar (30), -- server name

@ Username varchar (30), -- User Name

@ Password varchar (30), -- password

@ Tbname varchar (500), -- database... table name

@ Fdname varchar (30), -- field name

@ Fname varchar (1000), -- directory + file name, use/overwrite during processing: @ filename +. bak

@ Tj varchar (1000) = '', -- processing condition. 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 processes the response file name

, @ Fsize varchar (20) -- size of the file to be processed

, @ M_tbname varchar (50) -- temporary table name

, @ SQL varchar (8000)

-- Obtains the size of the imported file.

If @ isout = 1

Set @ fsize = '0'

Else

Begin

Create table # tb (varchar (20), int size

, Created on varchar (10), created on varchar (20)

The last write operation date is varchar (10), and the last write operation time is varchar (20)

, Last access date varchar (10), last access time varchar (20), feature int)

Insert into # tb

Exec master .. xp_getfiledetails @ fname

Select @ fsize = size from # tb

Drop table # tb

If @ fsize is null

Begin

Print 'file not found'

Return

End

End

-- Generate a data processing response 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 ended

Union all select null as format

)'

Exec (@ SQL)

Select @ fname_in = @ fname + '_ temp'

, @ SQL = 'bcp "'+ @ m_tbname +'" out "'+ @ fname_in

+ '"/S"' + @ servename

+ Case when isnull (@ username, '') = ''then''

Else '"/U"' + @ username end

+ '"/P"' + isnull (@ password, '') + '"/C'

Exec master .. xp_mongoshell @ SQL

-- Delete a temporary table

Set @ SQL = 'drop table' + @ m_tbname

Exec (@ SQL)

If @ isout = 1

Begin

Set @ SQL = 'bcp "select top 1' + @ fdname + 'from'

+ @ Tbname + case isnull (@ tj, '') when ''then''

Else 'where' + @ tj end

+ '"Queryout"' + @ fname

+ '"/S"' + @ servename

+ Case when isnull (@ username, '') = ''then''

Else '"/U"' + @ username end

+ '"/P"' + isnull (@ password ,'')

+ '"/I"' + @ fname_in + '"'

Exec master .. xp_mongoshell @ SQL

End

Else

Begin

-- Prepare a temporary table for Data Import

Set @ SQL = 'select top 0' + @ fdname + 'into'

+ @ M_tbname + 'from' + @ tbname

Exec (@ SQL)

-- Import data to a temporary table

Set @ SQL = 'bcp "'+ @ m_tbname +'" in "'+ @ fname

+ '"/S"' + @ servename

+ Case when isnull (@ username, '') = ''then''

Else '"/U"' + @ username end

+ '"/P"' + isnull (@ password ,'')

+ '"/I"' + @ fname_in + '"'

Exec master .. xp_mongoshell @ SQL

-- Import data to a formal table

Set @ SQL = 'update' + @ tbname

+ 'Set' + @ fdname + '= B.' + @ fdname

+ 'From' + @ tbname + 'A ,'

+ @ M_tbname + 'B'

+ Case isnull (@ tj, '') when''then''

Else 'where' + @ tj end

Exec (@ SQL)

-- Delete a temporary data processing table

Set @ SQL = 'drop table' + @ m_tbname

End

-- Delete the data processing response File

Set @ SQL = 'del '+ @ fname_in

Exec master .. xp_mongoshell @ SQL

Go

,