Bulk Insert sample
File location : C\SampleFolder
MS-SQL: SQL SERVER 2008 R2
데이블 생성한다.
CREATE TABLE [데이터베이스이름].[db_owner].[bulksample](
[ComputerName] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[ip] [varchar](50) NULL,
[code] [nchar](10) NULL,
[price] [numeric](18, 0) NULL,
[no] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_bulksample] PRIMARY KEY CLUSTERED ( [no] ASC )
) ON [PRIMARY] |
Data File (Comma delimited CSV) - sample.csv
| ComputerName |
Country |
IP |
Code |
Price |
No |
| Com1 |
CANADA |
192.168.1.1 |
0E1 |
2000 |
|
| Com2 |
CANADA |
192.168.1.2 |
0E2 |
2001 |
|
| Com3 |
CANADA |
192.168.1.3 |
0E3 |
2002 |
|
| Com4 |
CANADA |
192.168.1.4 |
0E4 |
2003 |
|
| Com5 |
CANADA |
192.168.1.5 |
0E5 |
2004 |
|
| Com6 |
CANADA |
192.168.1.6 |
0E6 |
2005 |
|
| Com7 |
CANADA |
192.168.1.7 |
0E7 |
2006 |
|
| Com8 |
CANADA |
192.168.1.8 |
0E8 |
2007 |
|
| Com9 |
CANADA |
192.168.1.9 |
0E9 |
2008 |
|
| Com10 |
CANADA |
192.168.1.10 |
0E10 |
2009 |
|
| Com11 |
CANADA |
192.168.1.11 |
0E11 |
2010 |
|
| Com12 |
CANADA |
192.168.1.12 |
0E12 |
2011 |
|
| Com13 |
CANADA |
192.168.1.13 |
0E13 |
2012 |
|
| Com14 |
CANADA |
192.168.1.14 |
0E14 |
2013 |
|
| Com15 |
CANADA |
192.168.1.15 |
0E15 |
2014 |
|
| Com16 |
CANADA |
192.168.1.16 |
0E16 |
2015 |
|
| Com17 |
CANADA |
192.168.1.17 |
0E17 |
2016 |
|
위의 샘플 데이터를 양식 없이 바로 입력했을 경우.. 데이터가 깨진다.
(Microsoft SQL Server management Studio에서는 제대로 입력됨.. 하지만 프로그램을 이용하면 다름과 같이 보인다.
BULK INSERT [데이터베이스이름].[db_owner].[bulksample]
FROM 'C:\SampleFolder\sample.csv'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
Go
|
ComputerName Country ip code price no
Com1 CANADA 192.168.1.1 0.00E+00 2000 77
Com2 CANADA 192.168.1.2 0.00E+00 2001 78
Com3 CANADA 192.168.1.3 0.00E+00 2002 79
Com4 CANADA 192.168.1.4 0.00E+00 2003 80
Com5 CANADA 192.168.1.5 0.00E+00 2004 81
Com6 CANADA 192.168.1.6 0.00E+00 2005 82
Com7 CANADA 192.168.1.7 0.00E+00 2006 83
Com8 CANADA 192.168.1.8 0.00E+00 2007 84
Com9 CANADA 192.168.1.9 0.00E+00 2008 85
Com10 CANADA 192.168.1.10 0.00E+00 2009 86
Com11 CANADA 192.168.1.11 0.00E+00 2010 87
Com12 CANADA 192.168.1.12 0.00E+00 2011 88
Com13 CANADA 192.168.1.13 0.00E+00 2012 89
Com14 CANADA 192.168.1.14 0.00E+00 2013 90
Com15 CANADA 192.168.1.15 0.00E+00 2014 91
Com16 CANADA 192.168.1.16 0.00E+00 2015 92
Com17 CANADA 192.168.1.17 0.00E+00 2016 93
그래서 포맷을 이용하여 code 부분을 수정해야한다.
C:> CD \ C:> CD SampleFolder c:\SampleFolder>bcp "[데이터베이스이름].db_ownder.bulksample" out "c:\SampleFolder\a.txt" -U[데이터베이스 유저 이름] password: 나중에 변경할 예정이르모 무조건 enter
Enter the file storage type of field ComputerName [char]: Enter prefix-length of
field ComputerName [2]:
Enter field terminator [none]:
Enter the file storage type of field Country [char]:
Enter prefix-length of field Country [2]:
Enter field terminator [none]:
Enter the file storage type of field ip [char]:
Enter prefix-length of field ip [2]:
Enter field terminator [none]:
Enter the file storage type of field code [nchar]:
Enter prefix-length of field code [2]:
Enter field terminator [none]:
Enter the file storage type of field price [numeric]:
Enter prefix-length of field price [1]:
Enter field terminator [none]:
Enter the file storage type of field no [numeric]:
Enter prefix-length of field no [1]:
Enter field terminator [none]:
Do you want to save this format information in a file? [Y/n]
Host filename [bcp.fmt]:
Starting copy...
17 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (17000.00 rows per sec.)
C:\SampleFolder>
C:\SampleFolder>
|
그후 C:\Samplefolder에 bcp.fmt 파일이 보인다.
이 파일을 sample.fmt로 복사한다.
메모장에서 열어보면 다음과 같다.
10.0
6
1 SQLCHAR 2 50 "" 1 ComputerName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 50 "" 2 Country SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 50 "" 3 ip SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 2 20 "" 4 code SQL_Latin1_General_CP1_CI_AS
5 SQLNUMERIC 1 19 "" 5 price ""
6 SQLNUMERIC 1 19 "" 6 no ""
|
이것을 다음과 같이 변경하고 실행할 경우 에러가 발생한다.
sample.fmt
10.0
6
1 SQLCHAR 0 50 "," 1 ComputerName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "," 2 Country SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "," 3 ip SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 20 "," 4 code SQL_Latin1_General_CP1_CI_AS
5 SQLNUMERIC 0 19 "," 5 price ""
6 SQLNUMERIC 0 19 "\r\n" 6 no "" |
다음과 같이 명령어 입력..
BULK INSERT [데이터베이스이름].[db_owner].[bulksample]
FROM 'C:\SampleFolder\sample.csv'
WITH (
FORMATFILE = 'C:\SampleFolder\sample.fmt',
FIRSTROW = 2
)
|
에러 메시지
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 4 (code).
Msg 7399, Level 16, State 1, Line 1
The
OLE DB provider "BULK" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
|
이것 때문에 고생을 많이 했다.ㅡㅡ;;
sample.fmt 을 다음과 같이 변경한다.
10.0
6
1 SQLCHAR 0 50 "," 1 ComputerName SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "," 2 Country SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "," 3 ip SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 20 "," 4 code SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 19 "," 5 price ""
6 SQLCHAR 0 19 "\r\n" 6 no ""
|
데이터가 정상적으로 입력된다.
Computer Name Country ip code price no
Com1 CANADA 192.168.1.1 0E1 2000 94
Com2 CANADA 192.168.1.2 0E2 2001 95
Com3 CANADA 192.168.1.3 0E3 2002 96
Com4 CANADA 192.168.1.4 0E4 2003 97
Com5 CANADA 192.168.1.5 0E5 2004 98
Com6 CANADA 192.168.1.6 0E6 2005 99
Com7 CANADA 192.168.1.7 0E7 2006 100
Com8 CANADA 192.168.1.8 0E8 2007 101
Com9 CANADA 192.168.1.9 0E9 2008 102
Com10 CANADA 192.168.1.10 0E10 2009 103
Com11 CANADA 192.168.1.11 0E11 2010 104
Com12 CANADA 192.168.1.12 0E12 2011 105
Com13 CANADA 192.168.1.13 0E13 2012 106
Com14 CANADA 192.168.1.14 0E14 2013 107
Com15 CANADA 192.168.1.15 0E15 2014 108
Com16 CANADA 192.168.1.16 0E16 2015 109
Com17 CANADA 192.168.1.17 0E17 2016 110 |
다음에 ASP.NET이나 C#의 어플리케이션에서 작업 후 결과를 확인해야겠다.
아직까지는 보류중..
