bcp

http://taeyong.tistory.com/9

 

http://www.sqler.com/

 

bcp crmIF.dbo.sa_f_sale_tot_view_d out e:\sa_f_sale_tot_view_d.txt -c -q -T -t ▦ -r \n\r

 

bcp crmIF.dbo.sa_f_sale_tot_view_d out e:\sa_f_sale_tot_view_d.txt -c -q -T -t ▦ -r \n\r

 

http://msdn.microsoft.com/ko-kr/library/ms190759.aspx

 

 

포멧 파일

 

http://msdn.microsoft.com/en-us/library/ms191479.aspx

 

Creating a Format File

http://msdn.microsoft.com/en-us/library/ms191516.aspx

 

bcp crmIF.dbo.sa_f_sale_tot_view_d   format nul -T -w -f e:\sa_f_sale_tot_view_d.fmt

 

 

BCP MSSQL

2005/11/11 16:12

http://blog.naver.com/iahalu/120019565053

1 step  BCP를 이용한 데이터 전송

목록

출처 : http://www.sqler.pe.kr

이번에 소개해 드릴 내용은 BCP라는 툴입니다. 아주 강력하고 유용한 툴이지요. 간단히 이 툴은 SQL서버에 데이터를 전송하거나 SQL서버로부터 전송받기위한  툴입니다. 극단적으로 다른 것은? 사용자가 정의한 텍스트 화일로부터 데이터를 전송한다는 의미 입니다.

역시나 많이 쓰이는 경우는?

1. 호환성이 없는 - SQL서버의 문자셋이 틀릴경우 또는 소팅(정렬) 방식이 틀릴경우 - 
2. 데이터베이스로 전송시나 마스터 데이터베이스를 리빌드 할때..
3. ODBC를 지원하지 않는 이기종의 데이터베이스에서 텍스트 화일로 저장한후 
4. SQL서버로 데이터를 로드 시킬때 등 많은 목적으로 사용 됩니다.

중요한 방식이니 알아두시면 아주 유용합니다. 간단히 BCP를 보시면 BCP는 단순히 SQL서버가 설치된 서버의 MS도스 프롬프트(실제는 가상 터미널)에서 실행합니다.
D:\>BCP
usage: BCP {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-6 6x file format] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
라는 엄청나게 많은???  옵션이 존재 한답니다. 물론 이것들을 다 사용하지는 않습니다. 필수적인 부분과 몇몇 부분만 사용을 하지요.그럼 실제 몇몇개의 샘플을 가지고 테스트 해 보겠습니다. 먼저 역시나 저희들의 가장 만만한 데이터베이스인 pubs 데이터베이스를 가지고
해 보겠습니다. 주의 : BCP는 모든 작업이 테이블 단위 입니다.
데이터를 우선 빼와서 텍스트 화일로 저장합니다.
C:\>bcp pubs..titles out bcp_data_titles.txt -c -q -T
Starting copy...
18 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 20 Avg 1 (900.00 rows per sec.)
실제 삽입 하는 명령은?
C:\>bcp pubs..titles2 in bcp_data_titles.txt -c -q -T

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
..titles2'.
인데. 되지 않습니다. 그 이유는?  해보신 분은 아시겠지만 먼제 테이블이 생성이 되어 있어야 하며 그후 BCP IN 이 가능해 집니다.
CREATE TABLE [dbo].[titles2] (
[title_id] [tid] NOT NULL ,
[title] [varchar] (100) NOT NULL ,
[type] [char] (12) NOT NULL ,
[pub_id] [char] (4) NULL ,
[price] [money] NULL ,
[advance] [money] NULL ,
[royalty] [int] NULL ,
[ytd_sales] [int] NULL ,
[notes] [varchar] (200) NULL ,
[pubdate] [datetime] NOT NULL 
)
GO
로 titles2를 생성하고 다시 함 해봅시다.
C:\>bcp pubs..titles2 in bcp_data_titles.txt -c -q -T
Starting copy...
18 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 290 Avg 16 (62.07 rows per sec.)
그럼 살짝 맛을 보셨으니 실제 어떠어떠한 옵션이 사용이 가능해 지는지 함 해보도록 하지용

2 step  BCP 분석하기

목록

출처 : http://www.sqler.pe.kr

그럼 실제 어떠어떠한 옵션이 사용이 가능해 지는지 함 해 봅시다.
Syntax

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-w] [-N] [-6] [-q] [-C code_page]
[-t field_term] [-r row_term]
[-i input_file] [-o output_file] [-a packet_size]
[-S server_name] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h "hint [,...n]"]

구문 정보는 위와 같았지요? 해당하는 인자들을 함 알아 보지요.

database_name
해당하는 데이터베이스 이름을 선택합니다. 위에서 저희는 pubs로 정했습니다.

owner해당 객체인 테이블의 소유자를 지정합니다. 물론 SA계정일 경우는 생략 가능합니다.
table_name
사용할 목적 테이블을 지정합니다.
저희는 OUT으로 데이터를 추출해낼 테이블의 이름은? titles 였으며..
IN으로 데이터를 로드시킨 테이블의 이름은? titles2였습니다.

view_name아울러 뷰역시 가능합니다.
뷰는 OUT으로는 물론 쉽게 빼올 수 있으며..
IN일시는? 뷰의 데이터삽입에서 공부한 삽입이 가능한 뷰만 IN이 가능해 집니다.

query
T-SQL구문의 쿼리역시 가능합니다.
문제는 compute 와 같은 결과 셋이 2가지 종류 이상인 쿼리는 첫번째 결과셋만 가져오거나  삽입 됩니다.
in | out | queryout | format
in 과 out은 잘 아실 겁니다.
queryout은 query와 연계되어 사용이 되며 format은 (-n, -c, -w, -6, or -N) 등의 옵션 명령의 조합으로 이루어지는 format화일과 연계되어  사용됩니다. 아울러 포맷 화일 사용시 반드시 -f 옵션을 사용합니다. 이 format화일은 아주 중요하므로 다시 다루니 요정도만 기억해 두세요.

data_file
실제로 데이터를 IN시는 불러올 텍스트 화일명 OUT시는 데이터를 저장할 텍스트 화일명을 지정합니다. 저희는 bcp_data_titles.txt 이라는 이름으로 사용 했었지요. -m 옵션은 max_errors 를 의미 합니다. 기본 10개 이내의 에러 생성시는 BCP 프로세스를 멈추지 않고 계속 진행하게 합니다.
아시다시피 텍스트 화일에서 정형화 되지 않았을 경우는?
문제가 심각해 집니다. 그러한 처리를 위해 최대 에러를 두었다고 생각 합니다.
하지만 저희는 거의 대부분 정형화된 데이터만을 다루게 될테니 한수 접으셔도 되지요.

-f
옵션은 format_file 을 지정합니다. 이 예에 대해서는 잠시 후 다루어 보니 조금만 기다리시면 됩니다.
-e
에러화일을 생성하는 의미 입니다. 이 옵션을 지정하지 않을시는 에러화일을 생성하지 않습니다.
일종의 LOG화일 이라고 생각하심 정확합니다.

-F
어느 행부터 가져올지를 지정합니다. 기본 첫번째 화일은 1번째 행부터 가져오지요.

-L
마지막 행을 지정합니다. 기본 마지막 화일은 0으로 EOF까지 가져온다는 의미 입니다.

-b batch_size
복사되는 데이터의 매 배치 로우의 수를 지정합니다.

-b batch_size
Specifies the number of rows per batch of data copied. 
Each batch is copied to the server as one transaction. 
SQL Server commits or rolls back, in the case of failure, the transaction for every batch. 
By default, all data in the specified data file is copied in one batch. 
Do not use in conjunction with the -h "ROWS_PER_BATCH = bb" option.

-n
데이터의 저장을 텍스트 타입이 아닌 BINARY형으로 (원시 데이터베이스 데이터 타입으로)  저장합니다. 장점이라면 속도가 아주 빠르다는 장점이 있습니다. 물론 호환성은 거의 없다고  보셔야 합니다. 텍스트가 아니기 때문입니다.
C:\>bcp pubs..titles out bcp_data_titles3.txt -n -T
Starting copy...
18 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 20 Avg 1 (900.00 rows per sec.)
C:\>bcp pubs..titles3 in bcp_data_titles3.txt -n -T
Starting copy...
18 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 50 Avg 2 (360.00 rows per sec.)

-c
데이터를 저장시 문자열형 데이터 타입을 사용합니다.  기본으로 컬럼 끝 알림 문자(컬럼 터미네이터)는 \t(탭) 이며  로우 끝 알림문자(로우 터미네이터)는 \n 입니다. 물론 위의 알림문자는 단지 텍스트 문서에만 사용되며 실제 IN으로 데이터베이스로  바꿀시는 사라집니다.

-w-c 옵션과 완전히 같으며 단지 유니코드 텍스트 문서로 저장합니다. 유니코드문서 여는 방법은 아시죠? 윈도우의 워드패드를 쓰신후 유니코드 문서 열기로  하셔야만 열립니다.

-N
이는 ANSI 확장 문자나 -n 옵션보다 빠른 속도를 내기 위할때 사용합니다. 6.5나 이하의 버젼에서는 호환성이 없습니다.

-6
6.5나 6.0 버젼의 데이터 타입에 완전히 맞게 텍스트 문서를 생성합니다.

-q
스페이스 공간과 같은 특수한 문자를 " " 로 처리해 줍니다. - 에러보정 기능입니다.

-C
code_page를 지정합니다.

-t
필드(컬럼) 터미네이터를 지정합니다.

-r
로우 터미네이터를 지정합니다.

-i
BCP IN에서 사용하는 인풋화일을 지정합니다.
-o
BCP OUT에서 사용하는 아웃풋 화일을 지정합니다.

-a
packet_size를 지정합니다.

-S
server_name을 지정합니다. 넷웍상의 원격 서버라도 상관 없습니다. 서버 이름을 지정하지 않을 시는 로컬서버가 선택 됩니다.

-U
login_id 를 지정합니다. 

-P
password 를 지정합니다. 물론 없을시는 지정하지 않아도 됩니다.

-T
트러스트 커넥션을 사용합니다. 로컬서버상에서 NT의 유저 권한으로 적절한 권한이 선택되어 있을시 -S 옵션과 -U 옵션과 -P 옵션을 없에고 -T 옵션 하나로 대체가 가능합니다.

-v
BCP버젼을 리턴합니다.

-R
지역의 통화. 날짜. 시간 형을 지정합니다. 기본적으로 무시하고 사용됩니다.

-k
BCP 시 널값을 계속 가지게 지정합니다.

-E
IDENTITY 컬럼을 지정합니다. 기본 없이 진행합니다.

-h "hint [,...n]" BCP시 힌트를 지정합니다. 6.5이하 에서는 사용이 불가합니다.
다음은 샘플 하나 더로 특수한 경우의 BCP입니다.

특정한 경우 입니다. 테이블 이름등에 공백이 들어 있을시 입니다.
bcp "Northwind..""Order Details""" out orders.txt -c -q -Sservername -Usa -Ppassword
OR
bcp "Northwind..[Order Details]" out orders.txt -c -q -Sservername -Usa -Ppassword
OR
bcp "Northwind..Order Details" out orders.txt -c -q -Sservername -Usa -Ppassword
수고하셨습니다.
다시 차근차근 보시면서 맨 처음으로 돌아가셔서 전 강좌의 맨처음에 보셨던 bcp 샘플을 다시 함 보시면 어떤 의미인지 감이 잡히실 겁니다. 다음은 실제로 특수한 경우의 샘플을 하나 보도록 합시다.

3 step  FORMAT 화일을 이용한 BCP

목록

출처 : http://www.sqler.pe.kr

이번엔 포맷 화일을 이용한 BCP입니다. 일전에 특수한 케이스의 BCP를 가지고 잠시 고민했던 것입니다. dgchoi라는 분이 질문 하셨던 샘플이랍니다. 이는 UNIX웨어에서 돌리던 모 데이터베이스를 SQL서버로 잠시 포팅하시던 중인듯 합니다. 어느 DBMS나 텍스트 화일로 OUT해주는 기능이 있어서 이를 이용해 TEXT로 아웃을 하였는 데 문제는 MSSQL에서 BCP 업을 하려는 도중 문제가 생기신듯 합니다. 그때 사용된 샘플 입니다.

--이하 5 로우의 샘플 입니다.
샘플열기

--이하 유닉스 DB상의 테이블 구조 입니다.
Structure for table: C:\APPS\ENTERP\TABLES\IMP13WHS.DBF
Number of data records: 60 
Date of last update: 11/16/99
Code Page: 949 
Field Field Name Type Width Dec Index Collate Nulls
1 IMP13WHS Character 4 No
2 IMP13NME Character 30 No
3 IMP13AD1 Character 30 No
4 IMP13AD2 Character 30 No
5 IMP13CTY Character 20 No
6 IMP13STA Character 10 No
7 IMP13ZIP Character 10 No
8 IMP13CTR Character 10 No
9 IMP13TEL Character 15 No
10 IMP13CON Character 15 No
11 IMP13ETC Character 1 No
** Total ** 176
이렇게 생긴 텍스트 데이터 입니다.  이때 일반적인 방식으로는 불가해 지더군요. 그래서 포맷 화일을 만들고 작업을 하게 되었습니다.

-- 이하 포맷 화일 정보 입니다.
포맷화일열기

아울러 이 포맷 화일도 반드시 다른이름으로 저장 하셔서

C:\에 샘플로 하나 저장해 두시길 바랍니다.  이렇게 생긴 화일 입니다. 그렇다면? 저 포맷 화일은 어떻게 만드는가?
그림을 보심 한눈에 이해가 가실 겁니다.

version
BCP의 버젼 입니다.
Number of fields
필드(컬럼)의 갯수 입니다.
Host file field order
원본 화일의 필드 순서 입니다.
Host file data type
원본 화일의 데이터 타입 입니다.
Prefix length
컬럼길이 지정 문자를 지정합니다.
Legal prefix lengths are 0, 1, 2, and 4. To avoid specifying the length prefix, set this to 0. 
To provide the most compact file storage when bulk copying data in native format to a data file, 
bcp precedes each field with one or more characters that indicates the length of the field. 
These characters are called length prefix characters. 
mk:@MSITStore:C:\MSSQL7\BOOKS\SQLBOL.CHM::/html/impt_bcp_13.htm 에서 
자세히 보실 수 있습니다.
Host file data length
원본 화일의 데이터 길이 입니다.
Terminator
컬럼 로우의 터미네이터 입니다.
Server column order
서버측에 저장될 컬럼의 순서 입니다.
Server column name
서버측에 저장될 컬럼의 이름 입니다.

그럼 실제로 저 5개의 샘플을 f5.fi 라는 이름으로 저장하고 포맷 화일을 D:\ 에 a.fmt라는 이름으로 저장한 후 쿼리 어낼라이져를 이용해 테이블을 생성하고
CREATE TABLE [dbo].[imp] (
[whs] [varchar] (4) NULL ,
[nme] [varchar] (30) NULL ,
[ad1] [varchar] (30) NULL ,
[ad2] [varchar] (30) NULL ,
[cty] [varchar] (20) NULL ,
[sta] [varchar] (10) NULL ,
[zip] [varchar] (10) NULL ,
[ctr] [varchar] (10) NULL ,
[tel] [varchar] (15) NULL ,
[con] [varchar] (15) NULL ,
[etc] [varchar] (1) NULL 
)
GO

돌려 보도록 하지요.

C:\>bcp pubs..imp in d:\f5.fi -f d:\a.fmt -T
Starting copy...
5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 601 Avg 120 (8.32 rows per sec.)

쿼리 어낼라이져 에서 돌려 보면??

select * from imp

결과는 직접 확인해 보세요.

어떠신지요?  조금 필이 오시나요???   아울러 코난이가 말씀 드리는 BCP 사용시의 팁앤텍 입니다.
1. BCP로 처리되는 화일은 텍스트 화일 입니다.
포통 유닉스 베이스에서 화일을 가져올시는? FTP를 씁니다.
주의점은 FTP에서 화일을 가져올 시 BINARY 타입 전송이 아닌 ASCII형으로 전송을 하셔야화일이 텍스트 화일 형태로 옵니다. 실제는 텍스트 화일 터미네이터만 존재하면 되지만 사실 귀찮더라도 텍스트형 전송으로 처리가 더 속편합니다.)

2. 한번에 BCP 사용이 완성된 적은 없었습니다.
한 5번 이상 테스트로 돌려봐야 그제서야 조금 답 보입니다.  노력해야만 결과를 보실 겁니다.

3. 포맷화일과 사용되는 데이터가 들어있는 텍스트 화일은 공백이나 문자열의 갯수에 아주아주 민감합니다.
예를 들어 데이터를 그냥 보기만 하다가 화일 첫줄에 공백이 들어가면?  완전 에러 잔치가 납니다. 아울러 포맷화일 역시 입니다. 데이터를 브라우징 하실때도 주의해서 보시길 바랍니다.

4. 속도에 민감해 지셔야 합니다.
900메가 짜리 텍스트 데이터 화일을 BCP IN 시킬때는 얼마의 시간이 걸리는가?  BCP IN시와 BCP OUT시의 속도를 항상 눈여겨 봐 두셔야 추후 정말 큰 작업이 생길시 예를들면 복구를 하는데 복구용 테입마져 깨진걸 알아서 결국 TEXT형으로 화일 백업한 데이터를 BCP IN으로 처리할 때 얼마의 시간내에 복구가 가능한가? 를 아셔야 합니다. 항상 속도를 눈여겨 보시길 바랍니다.
이정도면 충분하실듯 하네요.  수고하셨습니다.

다음 코난이의 강좌 이야기는?  또한 많은 분들이 어려워 하시고 힘들어 하시는  데이터베이스 백업 전략과 복구 전략 입니다. 제가 실무에서 사용하는 방식이구요. 많은 도움 되시길 바랍니다.