Import export of SQL binary files

Import export of/*--bcp-binary files

Support for import/export of Image,text,ntext fields

Image is suitable for binary files; text,ntext for text data files

Note: When importing, all rows that meet the criteria are overwritten

When exporting, all rows that meet the criteria are also shown in the specified file

This stored procedure is implemented in BCP only

Jiangjian 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,--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 + filename, to be used/overwritten during processing: @filename +.bak

@tj varchar (1000) = ',--processing condition. For data import, specify a table name prefix if the condition contains @fdname

@isout bit=1--1 Export ((default), 0 import

As

DECLARE @fname_in varchar (1000)--BCP processing answer file name

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

, @m_tbname varchar (50)--Temporary table name

, @sql varchar (8000)

--Gets the size of the import file

If @isout =1

Set @fsize = ' 0 '

Else

Begin

CREATE table #tb (optional name varchar (20), size int

, Creation date varchar (10), Creation time varchar (20)

, last write operation date varchar (10), last write operation time varchar (20)

, Last accessed date varchar (10), last access time varchar (20), attribute 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 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 Format

A

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_cmdshell @sql

--Delete temporary tables

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_cmdshell @sql

End

Else

Begin

--Prepare temporary tables for data import

Set @sql = ' Select top 0 ' + @fdname + ' into '

+ @m_tbname + ' from ' + @tbname

EXEC (@sql)

--Import data into 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_cmdshell @sql

--Import data into 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)

--Deleting a data-processing temporary table

Set @sql = ' drop table ' + @m_tbname

End

--delete Data processing answer file

Set @sql = ' del ' + @fname_in

EXEC master.. xp_cmdshell @sql

Go