'프로그래밍/MS-SQL'에 해당되는 글 8건

  1. 2013.07.20 Bulk insert with BCP - 2 [example]
  2. 2013.07.20 Bulk insert with BCP - 1
  3. 2013.02.23 Like Query 사용시 주의점
  4. 2013.02.21 SQL Joins
  5. 2013.02.21 SQL SERVER - CASE 문 - 중첩

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#의 어플리케이션에서 작업 후 결과를 확인해야겠다.


아직까지는 보류중..




Posted by 노을지기

대용량 데이터를 입력할 일 있어서 bulk insert로 입력하였다.

데이터가 많을 경우 아주 유용하게 사용하였다.


하지만 문제는 입력시 자동으로 포맷이 변경되는 경우이다.

예를 들어 ip나 0으로 시작하는 데이터는 자기 마음대로 포맷을 변경하였다.

ex) 0E1 -> 0x0000xxx

그래서 포맷을 지정하기 위해서 Format file을 이용하려고 하는데... 

[Host file datatype]에서 문제가 발행하였다.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility86.htm

Storage format에서 numeric 포맷을 SYBNUMERIC로 변경하여 넣으면 바로 에러가 발생한다.


그래서 방법을 찾던 중 다음 것을 발견하였다.

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

Using a Non-XML Format File


You can change the order of a column mapping by changing the order value for the column to indicate the position of the corresponding data field.

The following sample non-XML format file presents a format file, myTestOrder.fmt, that maps the fields in myTestOrder-c.txt to the columns of the myTestOrder table. For information about how to create the data file and table, see "Sample Table and Data File," earlier in this topic. The format file uses character data format.

The format file contains the following information:

          9.0
4
1       SQLCHAR       0       100     ","     3     Col3               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       100     ","     2     Col2               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       ","     1     Col1               ""
4       SQLCHAR       0       100     "\r\n"  4     Col4               SQL_Latin1_General_CP1_CI_AS

        

위에 같이 Col1 컬럼이 숫자형인데, SYBNUMERIC을 사용하지 않고, 바로 SQLCHAR을 사용하였다.

그후에 입력하니 바로 성공..ㅡㅡ;;

왜 이러는지는 나도 모름..ㅠㅠ

다음 페이지 예제로 확인 <-- 나중에 잊어 먹지 않기 위해서..



Posted by 노을지기

이것때문에 좀 시간을 허비했다.

인터넷 검색 기술도 없어서 그런지. 아무리 구글을 뒤져도 나오지않는 현상..ㅡㅡ;;

피곤해서 그런지 이젠 검색하기도 귀찮다..


C#에서 Dataset을 이용하여 데이터를 쿼리하여 datagridview에 뿌려주었다.

Dataset 설정에서 fill 에서는 % 을 넣을 수 없는 관계로 꼼수를 이용하여 사용하였다.

Dataset에서는

SELECT * FROM [TABLE_NAME] WHERE [TABLE_COLUMN] LIKE @[SEARCH];

로 설정하였다.


그후 C#에서 TEXTBOX_TextChanged 이벤트에 넣어서 "%" + TEXTBOX.TEXT + '%' 을 이용하여 사용하였다.

몇 번 잘 사용했는데,

문제는

1. 검색하다 전체를 선택한 후 숫자를 넣었을 경우 에러 (try catch 문 안 넣었음.. 에러 잡기 위하여)

2. 처음에 숫자를 넣었을 경우.

에러가 발생하였다.

단독 테스트를 했을 경우 문제가 없었는데.. 실제 사용할 경우 문제가 발생하였다.


이런 저런 검색 후 생각해낸 꼼수는..

protected string GetSearchValue(string value)
        {
            if (value.Length > 1)
                return "%" + value + "%";
            else
                return value + "%";
        }

숫자일 경우 % 문자가 처음에 갈 경우 문제가 발생하였다.

그래서 입력 단어 개수를 파악하여 1나일 경우 뒤에 '%' 붙이고,

2개 이상일 경우 '%' + 입력 단어 + '%'로 처리하였다.


그후엔 특별한 문제가 없는듯..ㅡㅡ;;

수정.. 처음엔 잘 되다가.. 문제가 다시 발생..

null 값 체크를 했는데도 같은 현상이 발생하였다..

DataSet query configuration에서  ( TABLE_COLUMN LIKE RTRIM(@name))


이렇게 수정하니 제대로 작동.. ㅇ

어렵구나.. 프로그램하는 시간보다 툴 사용 방법 찾는데 시간이 더 오래 걸리는듯..ㅠㅠ

Posted by 노을지기
2013. 2. 21. 03:52

출처: http://blog.daum.net/kkyagami/22

이 개념만 가지고 있으면 ... 문제 없을 듯.. Join에 대하여

이 아래 내용은 나중에 다시 정리해야겠다.


----------------- 참고 용 ===================

Mysql DB를 다룰 때 초보 수준에서 약간 중급으로 넘어가면서
흔히들 많이 어려워 하는 것이 Join 구문입니다.

먼저, 아래와 같은 테이블 두개가 있다고 합시다.
mysql> select * from demo_people;
+————+————–+——+
| name | phone | pid |
+————+————–+——+
| Mr Brown | 01225 708225 | 1 |
| Miss Smith | 01225 899360 | 2 |
| Mr Pullen | 01380 724040 | 3 |
+————+————–+——+

mysql> select * from demo_property;
+——+——+———————-+
| pid | spid | selling |
+——+——+———————-+
| 1 | 1 | Old House Farm |
| 3 | 2 | The Willows |
| 3 | 3 | Tall Trees |
| 3 | 4 | The Melksham Florist |
| 4 | 5 | Dun Roamin |
+——+——+———————-+

두 테이블은 pid 칼럼으로 엮여 있습니다.

먼저 두 테이블을 pid가 같은 것을 조건으로 일반적인 Join을 걸면
결과는 아래와 같습니다.

mysql> select name, phone, selling
from demo_people join demo_property
on demo_people.pid = demo_property.pid;

+———–+————–+———————-+
| name | phone | selling |
+———–+————–+———————-+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+———–+————–+———————-+

pid가 같은 조건이 있는 줄은 두 테이블 모두에서 모조리 나오게 됩니다.
어느테이블이 기준이랄 것이 없이 양쪽에서 줄들이 추가되는
형국이죠. 이 때 서로 같은 것이 존재하지 않는 줄은 아예 출력이
되지 않습니다.

반면, Left 혹은 right join은 기준 테이블, 즉 반드시 출력되는
테이블을 잡아 줍니다.
위의 SQL 구문에서 Left Join을 걸어 보면 아래와 같은 결과가 나옵니다.

mysql> select name, phone, selling
from demo_people left join demo_property
on demo_people.pid = demo_property.pid;
+————+————–+———————-+
| name | phone | selling |
+————+————–+———————-+
| Mr Brown | 01225 708225 | Old House Farm |
| Miss Smith | 01225 899360 | NULL |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+————+————–+———————-+

이 때 Left Join이기 때문에 왼쪽 테이블이 기준이 됩니다.
따라서, 왼쪽 테이블의 모든 Row가 결과값에 반드시 한줄 이상
나오는 보장을 받게 됩니다. 왼쪽 테이블(demo_people)에 해당하는
오른쪽 테이블의 pid가 여러개일경우 위와 같이 여러줄이 나옵니다.

반면, right join은 left join과 반대로 기준이 오른쪽 테이블입니다.
오른쪽 테이블은 반드시 한줄 이상 나오는 보장을 받게 되는 것이죠.
결과 값을 한번 보시죠.

mysql> select name, phone, selling
from demo_people right join demo_property
on demo_people.pid = demo_property.pid;

+———–+————–+———————-+
| name | phone | selling |
+———–+————–+———————-+
| Mr Brown | 01225 708225 | Old House Farm |
| Mr Pullen | 01380 724040 | The Willows |
| Mr Pullen | 01380 724040 | Tall Trees |
| Mr Pullen | 01380 724040 | The Melksham Florist |
| NULL | NULL | Dun Roamin |
+———–+————–+———————-+

왼쪽 테이블(demo_people)에 해당 pid 값이 같은 줄이 없어도,
오른쪽 테이블(demo_property)이 모두 나와야 하기 때문에,
null, null 값이 출력되게 된 것입니다.

INNER JOIN은 JOIN과 같고,
LEFT OUTER JOIN은 LEFT JOIN과 같고,
RIGHT OUTER JOIN은 RIGHT OUTER JOIN과 같습니다.

Posted by 노을지기

속도는 느리지만, 가끔 필요한 소스이다.

다른 방법이 있으면 알려주세요!!

CASE @TestVal
WHEN 1 THEN

            CASE @TestVal2
            WHEN 1 THEN 'First'
            ELSE
@TestVal2 END

WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END


일을 하다보니, 가끔 내부적으로 특정 데이터만 다시 뽑길 원해서 사용한다..ㅠㅠ

야매인듯..


CASE expressions can be used in SQL anywhere an expression can be used. Example of where CASE expressions can be used include in the SELECT list, WHERE clauses, HAVING clauses, IN lists, DELETE and UPDATE statements, and inside of built-in functions.

Two basic formulations for CASE expression
1) Simple CASE expressions
A simple CASE expression checks one expression against multiple values. Within a SELECT statement, a simple CASE expression allows only an equality check; no other comparisons are made. A simple CASE expression operates by comparing the first expression to the expression in each WHEN clause for equivalency. If these expressions are equivalent, the expression in the THEN clause will be returned.

Syntax:
CASE expression
WHEN expression1 THEN expression1
[[WHEN expression2 THEN expression2] [...]]
[ELSE expressionN]
END

Example:
DECLARE @TestVal INT
SET
@TestVal = 3
SELECT
CASE @TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END


2) Searched CASE expressions

A searched CASE expression allows comparison operators, and the use of AND and/or OR between each Boolean expression. The simple CASE expression checks only for equivalent values and can not contain Boolean expressions. The basic syntax for a searched CASE expressions is shown below:

Syntax:
CASE
WHEN Boolean_expression1 THEN expression1
[[WHEN Boolean_expression2 THEN expression2] [...]]
[ELSE expressionN]
END

Example:
DECLARE @TestVal INT
SET
@TestVal = 5
SELECT
CASE
WHEN @TestVal <=3 THEN 'Top 3'
ELSE 'Other'
END

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted by 노을지기
이전버튼 1 2 이전버튼