SQLÊý¾Ý¿âÖÐÓÃimageÀ´´æ´¢Îļþ
SQLÊý¾Ý¿âÖÐÓÃimageÀ´´æ´¢Îļþ,µ«SQLûÓÐÌṩֱ½ÓµÄ´æÈ¡ÎļþµÄÃüÁî.
/*--bcp ʵÏÖ¶þ½øÖÆÎļþµÄµ¼Èëµ¼³ö
Ö§³Öimage,text,ntext×ֶεĵ¼Èë/µ¼³ö
imageÊʺÏÓÚ¶þ½øÖÆÎļþ,°üÀ¨:WordÎĵµ,ExcelÎĵµ,ͼƬ,ÒôÀÖµÈ
text,ntextÊʺÏÓÚÎı¾Êý¾ÝÎļþ
×¢Òâ:µ¼Èëʱ,½«¸²¸ÇÂú×ãÌõ¼þµÄËùÓÐÐÐ
µ¼³öʱ,½«°ÑËùÓÐÂú×ãÌõ¼þµÄÐе¼³öµ½Ö¸¶¨ÎļþÖÐ
´Ë´æ´¢¹ý³Ì½öÓÃbcpʵÏÖ
-----------------*/
/*--µ÷ÓÃʾÀý
--Êý¾Ýµ¼³ö
exec p_binaryIO 'zj','','','acc_ÑÝʾÊý¾Ý..tb','img','c:\zj1.dat'
--Êý¾Ýµ¼Èë
exec p_binaryIO 'zj','','','acc_ÑÝʾÊý¾Ý..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),--·þÎñÆ÷Ãû³Æ
@username varchar (30), --̞
@password varchar (30), --ÃÜÂë
@tbname varchar (500), --Êý¾Ý¿â..±íÃû
@fdname varchar (30), --×Ö¶ÎÃû
@fname varchar (1000), --Ŀ¼+ÎļþÃû,´¦Àí¹ý³ÌÖÐҪʹÓÃ/¸²¸Ç:@filename+_temp
@tj varchar (1000)='', --´¦ÀíÌõ¼þ.¶ÔÓÚÊý¾Ýµ¼Èë,Èç¹ûÌõ¼þÖаüº¬@fdname,ÇëÖ¸¶¨±íÃûǰ׺
@isout bit=1 --1µ¼³ö((ĬÈÏ),0µ¼Èë
AS
declare @fname_in varchar(1000) --bcp´¦ÀíÓ¦´ðÎļþÃû
,@fsize varchar(20) --Òª´¦ÀíµÄÎļþµÄ´óС
,@m_tbname varchar(50) --ÁÙʱ±íÃû
,@sql varchar(8000)
--ÔòÈ¡µÃµ¼ÈëÎļþµÄ´óС
if @isout=1
set @fsize='0'
else
begin
create table #tb(¿ÉÑ¡Ãû varchar(20),´óС int
,´´½¨ÈÕÆÚ varchar(10),´´½¨Ê±¼ä varchar(20)
,ÉÏ´Îд²Ù×÷ÈÕÆÚ varchar(10),ÉÏ´Îд²Ù×÷ʱ¼ä varchar(20)
,ÉϴηÃÎÊÈÕÆÚ varchar(10),ÉϴηÃÎÊʱ¼ä varchar(20),ÌØÐÔ int)
insert into #tb
exec master..xp_getfiledetails @fname
select @fsize=´óС from #tb
drop table #tb
if @fsize is null
begin
print 'ÎļþδÕÒµ½'
return
end
end
--Éú³ÉÊý¾Ý´¦ÀíÓ¦´ðÎļþ
set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
set @sql='selec
Ïà¹ØÎĵµ£º
ÔÚWindows 2003 sp1·þÎñÆ÷ÉÏȱʡ°²×° MS SQL Server 2005 ¼òÌåÖÐÎÄÆóÒµ°æ£¬ÔÚÁ¬½Ó·þÎñÆ÷ʱÏÔʾ“²»ÔÊÐíÔ¶³ÌÁ¬½Ó”¡£
¾ßÌåÏÔʾÈçÏ£º(xxxxxsqlΪ·þÎñÆ÷Ãû£¬ÔÚ±¾µØ²Ù×÷)
C:\Documents and Settings\Administrator>sqlcmd -S xxxxxsql
HResult 0x2£¬¼¶±ð 16£¬×´Ì¬ 1
ÃüÃû¹ÜµÀÌṩ³ÌÐò: ÎÞ·¨´ò¿ªÓë SQL Server ......
c06 --------------7545623Ìõ
c05---------------1032652Ìõ
vc01--------------427741Ìõ
SQL Statement from editor:
select c0602 "ÉÌÆ·±àÂë",c0625 "ÉÌÆ·ÌõÂë",
c0103 "ÉÌÆ·Ãû³Æ",c0104 "¹æ¸ñ",c060 ......
Oracle µÄSQL*LOADER¿ÉÒÔ½«ÍⲿÊý¾Ý¼ÓÔص½Êý¾Ý¿â±íÖС£ÏÂÃæÊÇSQL*LOADERµÄ»ù±¾Ìص㣺
1£©ÄÜ×°È벻ͬÊý¾ÝÀàÐÍÎļþ¼°¶à¸öÊý¾ÝÎļþµÄÊý¾Ý
2£©¿É×°Èë¹Ì¶¨¸ñʽ£¬×ÔÓɶ¨½çÒÔ¼°¿É¶È³¤¸ñʽµÄÊý¾Ý
3£©¿ÉÒÔ×°Èë¶þ½øÖÆ£¬Ñ¹ËõÊ®½øÖÆÊý¾Ý
4£©Ò»´Î¿É¶Ô¶à¸ö±í×°ÈëÊý¾Ý
5£©Á¬½Ó¶à¸öÎïÀí¼Ç¼װµ½Ò»¸ö¼Ç¼ÖÐ
6£©¶ÔÒ»µ¥¼Ç¼·Ö½âÔÙ×°Èëµ½± ......
SQL*LOADERÊÇORACLEµÄÊý¾Ý¼ÓÔع¤¾ß£¬Í¨³£ÓÃÀ´½«²Ù×÷ϵͳÎļþǨÒƵ½ORACLEÊý¾Ý¿âÖС£SQL*LOADERÊÇ´óÐÍÊý¾Ý
²Ö¿âÑ¡ÔñʹÓõļÓÔØ·½·¨¡£
¡¡¡¡ÔÚNTÏ£¬SQL*LOADERµÄÃüÁîΪSQLLDR£¬ÔÚUNIXÏÂÒ»°ãΪsqlldr/sqlload¡£
¡¡¡¡ÈçÖ´ÐУºd:\oracle>sqlldr
SQL*Loader: Release 8.1.6.0.0 - Production on ÐÇÆÚ¶þ 1ÔÂ 8 11:06:42 2 ......
CREATE PROCEDURE dbo.UspOutputData
@tablename sysname
AS
declare @column varchar(1000)
declare @columndata varchar(1000)
declare @sql varchar(4000)
declare @xtype tinyint
declare @name sysname
declare @objectId int
declare @objectname sysname
declare @ident int
set nocount on
......