/********* 엑셀로 내보내기
exec master..xp_cmdshell 'bcp Setb.dbo.shanghu out c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/************* 엑셀 가져오기
선택하다 *
opendatasource('microsoft.jet.oledb.4.0',
'데이터 소스="c:test.xls";사용자 ID=관리자;비밀번호=;확장 속성=excel 5.0')...xactions
Cast(cast(계정 번호는 숫자(10,2))로 nvarchar(255))+' ' 변환된 별칭으로 선택하세요.
opendatasource('microsoft.jet.oledb.4.0',
'데이터 소스="c:test.xls";사용자 ID=관리자;비밀번호=;확장 속성=excel 5.0')...xactions
/** 텍스트 파일 가져오기
exec master..xp_cmdshell 'c:dt.txt -c -sservername -usa -ppassword의 bcp dbname..tablename'
/** 텍스트 파일 내보내기
exec master..xp_cmdshell 'bcp "dbname..tablename" out c:dt.txt -c -sservername -usa -ppassword'
이 문장은 따옴표로 묶어야 합니다.
또는
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:dt.txt -c -sservername -usa -ppassword'
쉼표로 구분된 txt 텍스트로 내보내기
exec master..xp_cmdshell 'bcp "라이브러리 이름..테이블 이름" out "d:tt.txt" -c -t ,-u sa -p 비밀번호'
대량 삽입 라이브러리 이름..테이블 이름
'c:test.txt'에서
와 함께 (
필드 터미네이터 = ';',
행 종결자 = 'n'
)
--/* dbase iv 파일
에서 *를 선택하세요
openrowset('microsoft.jet.oledb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','[고객 데이터 4.dbf]에서 * 선택')
---*/
--/* dbase iii 파일
에서 *를 선택하세요
openrowset('microsoft.jet.oledb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','[고객 데이터 3.dbf]에서 * 선택')
---*/
--/* foxpro 데이터베이스
openrowset('msdasql',
'드라이버=Microsoft Visual Foxpro 드라이버;sourcetype=dbf;sourcedb=c:',
'[aa.dbf]에서 * 선택')
---*/
/********************DBF 파일 가져오기********************/
openrowset('msdasql',
'드라이버=Microsoft Visual Foxpro 드라이버;
소스DB=e:vfp98data;
소스 유형=dbf',
'국가별 고객에서 * 선택 != "usa"를 국가별로 주문')
가다
/************************ dbf로 내보내기 ***************/
생성된 구조(예: 기존) foxpro 테이블로 데이터를 내보내려면 다음 SQL 문을 직접 사용할 수 있습니다.
openrowset('msdasql',
'드라이버=Microsoft Visual Foxpro 드라이버;sourcetype=dbf;sourcedb=c:',
'[aa.dbf]에서 * 선택')
테이블에서 *를 선택하세요
설명하다:
sourcedb=c:는 foxpro 테이블이 있는 폴더를 지정합니다.
aa.dbf는 foxpro 테이블의 파일 이름을 지정합니다.
/********************액세스하기 위해 내보내기************************/
openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',a 테이블) 데이터베이스 이름에서 *를 선택합니다..b 테이블
/********************가져오기 액세스************************/
openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',테이블)
********************* xml 파일 가져오기
@idoc int 선언
@doc varchar(1000) 선언
--샘플 XML 문서
@doc ='로 설정
<루트>
<고객 cid= "c1" name="janine" city="issaquah">
<order oid="o1" date="1996/1/20" amount="3.5" />
<order oid="o2" date="4/30/1997" amount="13.4">고객이 매우 만족했습니다.
</order>
</고객>
<고객 cid="c2" name="ursula" city="oelde" >
<order oid="o3" date="1999/7/14" amount="100" note="파란색으로 포장하세요.
흰색 빨간색">
<긴급>중요</긴급>
행복한 고객.
</order>
<order oid="o4" date="1996/1/20" amount="10000"/>
</고객>
</root>
'
-- XML 문서의 내부 표현을 만듭니다.
exec sp_xml_preparedocument @idoc 출력, @doc
-- openxml 행 집합 공급자를 사용하여 select 문을 실행합니다.
선택하다 *
openxml에서(@idoc, '/root/customer/order', 1)
(oid char(5),
주석 ntext 'text()')
exec sp_xml_removedocument @idoc
/************************전체 데이터베이스 가져오기************************** ***** *******************/
bcp를 사용하여 구현된 저장 프로시저
/*
데이터 가져오기/내보내기를 위한 저장 프로시저 구현
다양한 매개변수에 따라 전체 데이터베이스/단일 테이블 호출 예제를 가져오거나 내보낼 수 있습니다.
--export 호출 예
----단일 테이블 내보내기
exec file2table 'zj','','','xzkh_sa..지역 정보','c:zj.txt',1
----전체 데이터베이스 내보내기
exec file2table 'zj','','','xzkh_sa','c:docman',1
--가져오기 호출 예
----단일 테이블 가져오기
exec file2table 'zj','','','xzkh_sa..지역 정보','c:zj.txt',0
----전체 데이터베이스 가져오기
exec file2table 'zj','','','xzkh_sa','c:docman',0
*/
존재하는 경우(name='file2table' 및 objectproperty(id,'isprocedure')=1인 sysobjects에서 1 선택)
삭제 프로시저 file2table
가다
프로시저 file2table 생성
@servername varchar(200) --서버 이름
,@username varchar(200) --사용자 이름, nt 확인 방법을 사용하는 경우 비어 있습니다.''
,@password varchar(200) --비밀번호
,@tbname varchar(500) --database.dbo.table 이름. .dbo.table 이름을 지정하지 않으면 데이터베이스의 모든 사용자 테이블을 내보냅니다.
,@filename varchar(1000) --가져오기/내보내기 경로/파일 이름입니다. @tbname 매개변수가 전체 데이터베이스를 내보내도록 지시하는 경우 이 매개변수는 파일 저장 경로이며 파일 이름은 자동으로 테이블 이름.txt를 사용합니다.
,@isout bit --1은 내보내기, 0은 가져오기
~처럼
@sql varchar(8000) 선언
if @tbname like '%.%.%' --테이블 이름을 지정하면 단일 테이블을 직접 내보냅니다.
시작하다
@sql='bcp '+@tbname 설정
+case when @isout=1 then ' out ' else ' in ' end
+' " '+@filename+' " /w'
+' /s '+@서버 이름
+isnull(@username,'')=''인 경우 '' else ' /u '+@username end
+' /p '+isnull(@password,'')
exec master..xp_cmdshell @sql
끝
또 다른
시작 - 전체 데이터베이스 내보내기, 커서 정의 및 모든 사용자 테이블 제거
@m_tbname varchar(250) 선언
맞다면(@filename,1)<>'' set @filename=@filename+''
set @m_tbname=' '+@tbname+'..sysobjects 에서 이름 선택을 위한 #tb 커서 선언(xtype=''u''')
exec(@m_tbname)
#tb 열어
#tb에서 @m_tbname으로 다음 항목을 가져옵니다.
@@fetch_status=0인 동안
시작하다
@sql='bcp '+@tbname+'..'+@m_tbname 설정
+case when @isout=1 then ' out ' else ' in ' end
+' " '+@filename+@m_tbname+'.txt " /w'
+' /s '+@서버 이름
+isnull(@username,'')=''인 경우 '' else ' /u '+@username end
+' /p '+isnull(@password,'')
exec master..xp_cmdshell @sql
#tb에서 @m_tbname으로 다음 항목을 가져옵니다.
끝
#tb 닫기
#tb 할당 해제
끝
가다
/************************엑셀에서 txt************************ ****** *******************/
사용하고 싶다
opendatasource(...)에서 *를 opendatasource(...)로 선택하세요.
엑셀 파일의 내용을 텍스트 파일로 가져오는 구현
엑셀에 2개의 열이 있다고 가정해 보겠습니다. 첫 번째 열은 이름, 두 번째 열은 계좌번호(16자리)입니다.
그리고 은행 계좌번호를 텍스트 파일로 내보낸 후 두 부분으로 나누어 처음 8자리와 마지막 8자리를 구분합니다.
위의 구문을 이용하여 삽입하려면 텍스트 파일이 존재해야 하며 이름, 은행계좌번호 1, 은행계좌번호 2 한 줄이 있어야 합니다.
그런 다음 다음 명령문을 사용하여 삽입할 수 있습니다. 파일 이름과 디렉터리는 실제 상황에 따라 수정될 수 있습니다.
삽입하다
오픈데이터소스('microsoft.jet.oledb.4.0'
,'텍스트;hdr=예;데이터베이스=c:'
)...[aa#txt]
--,aa#txt)
---*/
이름 선택, 은행계좌번호 1=왼쪽(은행계좌번호, 8), 은행계좌번호 2 = 오른쪽(은행계좌번호, 8)
~에서
오픈데이터소스('microsoft.jet.oledb.4.0'
,'excel 5.0;hdr=yes;imex=2;데이터베이스=c:a.xls'
--,시트1$)
)...[시트1$]
텍스트 파일을 직접 삽입하고 생성하려면 bcp를 사용하십시오.
@sql varchar(8000),@tbname varchar(50) 선언
--먼저 Excel 테이블의 내용을 전역 임시 테이블로 가져옵니다.
@tbname='[##temp'+cast(newid() as varchar(40))+']' 선택
,@sql='이름 선택, 계좌번호 1=왼쪽(은행계좌번호, 8), 은행계좌번호 2 = 오른쪽(은행계좌번호, 8)
에서 '+@tbname+' 으로
opendatasource(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=yes;imex=2;database=c:a.xls''
)...[시트1$]'
exec(@sql)
--그런 다음 bcp를 사용하여 전역 임시 테이블에서 텍스트 파일로 내보냅니다.
set @sql='bcp " '+@tbname+' " out "c:aa.txt" /s"(로컬)" /p"" /c'
exec master..xp_cmdshell @sql
--임시 테이블 삭제
exec('테이블 삭제 '+@tbname )
bcp를 사용하여 데이터베이스로 파일을 가져오고 내보내는 저장 프로시저:
/*--bcp-바이너리 파일 가져오기 및 내보내기
이미지, 텍스트, ntext 필드 가져오기/내보내기 지원
image는 바이너리 파일에 적합하고, ntext는 텍스트 데이터 파일에 적합합니다.
참고: 가져올 때 조건을 충족하는 모든 행을 덮어씁니다.
내보낼 때 조건을 충족하는 모든 줄도 지정된 파일로 내보내집니다.
이 저장 프로시저는 bcp만 사용하여 Zou Jian 2003.08을 구현합니다.------*/
/*--호출 예
--데이터 내보내기
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--데이터 내보내기
exec p_binaryio 'zj','','','acc_demo 데이터..tb','img','c:zj1.dat','',0
---*/
존재하는 경우(id = object_id(n'[dbo].[p_binaryio]') 및 objectproperty(id, n'isprocedure') = 1인 dbo.sysobjects에서 * 선택)
삭제 절차 [dbo].[p_binaryio]
가다
proc p_binaryio 생성
@servename varchar (30),--서버 이름
@username varchar (30), --username
@password varchar (30), --password
@tbname varchar (500), --database..테이블 이름
@fdname varchar (30), --필드 이름
@fname varchar (1000), --directory + 파일 이름, 처리 중 사용/덮어쓰기: @filename+.bak
@tj varchar (1000)='', --processing 조건 데이터 가져오기의 경우 조건에 @fdname이 포함되어 있으면 테이블 이름 접두사를 지정하십시오.
@isout bit=1 --1 내보내기((기본값), 0 가져오기
~처럼
@fname_in varchar(1000) 선언 --bcp 처리 응답 파일 이름
,@fsize varchar(20) --처리할 파일의 크기
,@m_tbname varchar(50) --임시 테이블 이름
,@sql varchar(8000)
--가져온 파일의 크기를 가져옵니다.
@isout=1인 경우
@fsize='0' 설정
또 다른
시작하다
테이블 #tb 생성(선택적 이름 varchar(20), 크기 int
, 생성 날짜 varchar(10), 생성 시간 varchar(20)
, 마지막 쓰기 작업 날짜 varchar(10), 마지막 쓰기 작업 시간 varchar(20)
,마지막 액세스 날짜 varchar(10), 마지막 액세스 시간 varchar(20),characteristic int)
#tb에 삽입
exec master..xp_getfiledetails @fname
#tb에서 @fsize=size를 선택하세요.
드롭 테이블 #tb
@fsize가 null인 경우
시작하다
'파일을 찾을 수 없습니다' 인쇄
반품
끝
끝
--데이터 처리 응답 파일 생성
@m_tbname='[##temp'+cast(newid()를 varchar(40))+']'로 설정
@sql='select *를 '+@m_tbname+' 로 설정(
유형으로 null을 선택하세요.
Union 모두 접두사로 0을 선택합니다.
Union 모두 길이로 '+@fsize+'를 선택합니다.
유니온 모두 끝으로 null을 선택합니다.
유니온 모두 형식으로 null을 선택합니다.
) 아'
exec(@sql)
@fname_in=@fname+'_temp'를 선택하세요.
,@sql='bcp " '+@m_tbname+' " out " '+@fname_in
+'" /s" '+@servename
+ isnull(@username,'')='' 다음 ''인 경우
else '" /u" '+@사용자 이름 끝
+'" /p"'+isnull(@password,'')+'" /c'
exec master..xp_cmdshell @sql
--임시 테이블 삭제
@sql='테이블 삭제 '+@m_tbname 설정
exec(@sql)
@isout=1인 경우
시작하다
set @sql='bcp "'에서 상위 1개 '+@fdname+' 선택
+@tbname+case isnull(@tj,'') when '' then ''
else ' 여기서 '+@tj 끝
+'" 쿼리아웃 " '+@fname
+'" /s" '+@servename
+ isnull(@username,'')='' 다음 ''인 경우
else '" /u" '+@사용자 이름 끝
+'" /p"'+isnull(@password,'')
+'" /i" '+@fname_in+'"'
exec master..xp_cmdshell @sql
끝
또 다른
시작하다
--데이터 가져오기를 위한 임시 테이블 준비
set @sql='상위 0개 선택 '+@fdname+' 를 '로 설정
+@m_tbname+' from ' +@tbname
exec(@sql)
--임시 테이블로 데이터 가져오기
set @sql='bcp " '+@m_tbname+' " in " '+@fname
+'" /s" '+@servename
+ isnull(@username,'')='' 다음 ''인 경우
else '" /u" '+@사용자 이름 끝
+'" /p"'+isnull(@password,'')
+'" /i" '+@fname_in+'"'
exec master..xp_cmdshell @sql
--공식 테이블로 데이터 가져오기
@sql='업데이트 '+@tbname 설정
+' set '+@fdname+'=b.'+@fdname
+' '+@tbname+' 에서 a,'
+@m_tbname+' b'
+case isnull(@tj,'') '' 다음 ''
else ' 여기서 '+@tj 끝
exec(@sql)
--데이터 처리 임시 테이블 삭제
@sql='테이블 삭제 '+@m_tbname 설정
끝
--데이터 처리 응답 파일 삭제
@sql='del '+@fname_in 설정
exec master..xp_cmdshell @sql