보미네

ssis를 이용해 오라클로부터 데이터를 받아 오고자 한다면, sql server ent. 버전이면 attunity 를 쓰면 가장 빠르다.

놀라운 속도가 나온다. 그러나, 그냥 몇건 안되는 데이터만 받아오면 된다면 꼭 ent. 를 쓸 필요없이 std. 로도 가능하다.

bol 에서 ent 만 attunity driver 를 지원한다고 되어 있지만 드라이버를 std 에 깔아도 정상 동작한다. -_-  (by 김상수 수석)

ssis 는 32bit / sql server agent 64bit 이니 2가지 oracle client 를 깔아야 한다.

win32_11gR2_client.zip

win64_11gR2_client.zip

두개를 깔때 admin 모드로 설치하면되고 설치가 완료되면 리부팅 한다.

tnsnames.ora 를 설정해야 한다.

깔고나면

D:\app\Naver\product\11.2.0\client_1\network 와 유사한 폴더가 생긴다. 이건 32bit 가 깔린 폴더

D:\app\Naver\product\11.2.0\client_2\network 와 유사한 폴더가 생긴다. 이건 64bit 가 깔린 폴더

두 폴더 아래에 admin 이라는 폴더를 만들고 tnsnames.ora 를 복사해 넣는다.

X =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.9.2.3)(PORT = 12345))
    )
    (CONNECT_DATA =
      (SID = X)
    )
  )

이럼 끝~

그냥 실행도 되고 agent 도 잘 돌아간다.

http://m.blog.daum.net/_blog/_m/articleView.do?blogid=0S8vK&articleno=110&maxNo=123&minNo=102&maxDt=20140711173404&minDt=20110830080000&maxListNo=0&minListNo=0&maxListDt=&minListDt=&currentPage=1&beforePage=1&categoryId=#

https://technet.microsoft.com/en-us/library/ee470675(v=sql.100).aspx

IF OBJECT_ID('DimDate') IS NOT NULL
DROP TABLE DimDate
GO

CREATE TABLE DimDate
(--IDX INT
--,
DDATE date
,IFULLDATE int
,IYEAR INT
,IMONTH INT
,IDAY INT
,IQUARTER INT
,IWEEK INT
,IWEEKDAY INT
,VWEEKDAY VARCHAR(10)
,HOLIDAY_YN INT
,HOLIDAY_NAME VARCHAR(100)
)
GO

INSERT INTO DimDate (
-- IDX
--,
DDATE
,IFULLDATE
,IYEAR
,IMONTH
,IDAY
,IQUARTER
,IWEEK
,IWEEKDAY
,VWEEKDAY
)
SELECT top 2191 -- ROW_NUMBER() OVER (ORDER BY (SELECT 1)) IDX -- 2015년 말까지
 CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1
 , cast(CONVERT(VARCHAR(10), CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, 112) as int)
 , DATEPART(YEAR, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(MONTH, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(DAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(QUARTER, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(WEEK, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(WEEKDAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , CASE DATEPART(WEEKDAY, CAST('20100101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
  WHEN 1 THEN '일'
  WHEN 2 THEN '월'
  WHEN 3 THEN '화'
  WHEN 4 THEN '수'
  WHEN 5 THEN '목'
  WHEN 6 THEN '금'
  WHEN 7 THEN '토'
  ELSE NULL END
FROM SYSOBJECTS A
, SYSOBJECTS B
--, SYSOBJECTS C

UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20090101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090125'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090126'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20090127'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20090301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20090502'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20090505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20090606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20090815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20091002'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석,개천절' WHERE IFULLDATE = '20091003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20091004'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20091225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20100101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100213'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100214'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20100215'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20100301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20100505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20100521'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20100606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20100815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100921'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100922'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20100923'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20101003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20101225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20110101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110202'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110203'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20110204'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20110301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20110505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20110510'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20110606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20110815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110911'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110912'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20110913'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20111003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20111225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20120101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120122'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120123'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20120124'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20120301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20120505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20120528'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20120606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20120815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20120929'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20120930'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20121001'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20121003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20121225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20130101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130209'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130210'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20130211'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20130301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20130505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20130517'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20130606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20130815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130918'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130919'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20130920'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20131003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20131225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20140101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140130'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140131'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20140201'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20140301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20140505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20140506'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20140606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20140815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140907'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140908'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20140909'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20141003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20141225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20150101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150218'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150219'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20150220'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20150301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20150505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20150525'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20150606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20150815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150926'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150927'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20150928'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20151003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20151225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20160101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160207'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160208'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20160209'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20160301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20160505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20160514'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20160606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20160815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160914'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160915'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20160916'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20161003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20161225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20170101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170127'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170128'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20170129'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20170301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20170503'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20170505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20170606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20170815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절,추석' WHERE IFULLDATE = '20171003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20171004'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20171005'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20171225'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE IFULLDATE = '20180101'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180215'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180216'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE IFULLDATE = '20180217'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE IFULLDATE = '20180301'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE IFULLDATE = '20180505'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE IFULLDATE = '20180522'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE IFULLDATE = '20180606'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE IFULLDATE = '20180815'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180923'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180924'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE IFULLDATE = '20180925'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE IFULLDATE = '20181003'
UPDATE DBO.DimDate SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE IFULLDATE = '20181225'

UPDATE DimDate
SET HOLIDAY_YN = 1
WHERE VWEEKDAY IN ('토','일')

UPDATE DimDate
SET HOLIDAY_YN = 0
, HOLIDAY_NAME = ''
WHERE HOLIDAY_NAME IS NULL

DELETE DimDate WHERE IFULLDATE > '20181225'
GO

SELECT * FROM DimDate

신기술에 대한 데모와 스크립트를 제공한다.

http://social.technet.microsoft.com/wiki/contents/articles/6982.sql-server-2012-developer-training-kit-bom.aspx?Sort=MostRecent

https://en.wikipedia.org/wiki/Complex_event_processing

Notable vendors and products[edit]

• SQLstream SQLstream’s stream processing platform, s-Server, provides a relational stream computing platform for analyzing large volumes of service, sensor and machine and log file data in real-time.
• Microsoft StreamInsight Microsoft CEP Engine implementation ^[20]
• openPDC — A set of applications for processing streaming time-series data in real-time.
• Apama - A Complex Event Processing Platform that monitors rapidly moving event streams, detects and analyzes important patterns, and takes action according to rules.^[21]
• StreamBase Systems - A visual development platform and high performance event server for rapidly building and deploying real-time event-based applications. - Now Owned by TIBCO Software
• SAP ESP - A low-latency, rapid development and deployment platform that allows processing multiple streams of data in real time ^[22]
• TIBCO BusinessEvents & Streambase - CEP platform and High Performance Low Latency Event Stream Processing
• WebSphere Business Events
• Informatica RulePoint - High performant, scalable solution by Informatica.
• Drools Fusion
• GigaSpaces XAP
• Oracle Event Processing - A solution for building applications to filter, correlate, and process events in real time.
• Esper Complex event processing for Java and C#.
• Siddhi Complex event processing written in Java. Designed as part of a series of middleware components.

https://www.youtube.com/watch?v=UWq7uSXaoz8 내용중

제품

상용제품

TIBCO : TIBCO BusinessEvents : 업계 최고 제품

StreamBase Systems : StreamBase CEP : 정교한 CEP 제품

SAP : Sybase Event Stream Processor : Sysbase 인수

IBM : InfoSphere Streams : 테러 방지 목적으로 시작 멀티미디어 스트림 프로세싱 가능

Microsoft : StreamInsight : 

Informatica : RulePoint

오픈소스

Esper

quick start project https://github.com/tedwon/cep-esper-quick-start

입력 (이벤트를 수집) 어뎁터를 만들어야 함

처리 (실시간 처리) 

결과 (다양한 이벤트의 복합 영향도 패턴 인식 등을 구현할 수 있음)

EPL SQL 형식의 Event 처리 스크립트 언어

• Event filtering 
• Sliding data windows and aggregation
• grouped windows and output rate limiting
• joins and outer joins
• subquery
• historical or reference data

Event Streams 에서 EPL Statements 를 이용해 데이터를 검색할 수 있음

join 은 RDBMS 나 Apache HBase UDF 와 할 수 있다. 

Example

from pattern [a -> (b and c)]

b c 이벤트가 발생하고 a 가 발생하면 알람을 달라 라고 event 의 correlation 을 정할 수 있음 

개발전략

모니터링 이벤트 정의

이벤트 타입(포멧) 정의

EPL 정의

output adapter 개발 등록

input adapter 개발 등록

CEP 개발 주요 포인트

Runtime 환경 결정

Zero-downtime 지원 여부 결정

Runtime 시 EPL 업데이트 지원 여부 결정

Event 타입과 용량 결정 

솔루션 만들고 JBOSS RHQ 로 모니터링

첨부는 실습 메모이다. 

timeSeries.docx

틀린부분? 없다.....

https://technet.microsoft.com/ko-kr/library/cc879295(v=sql.110).aspx

https://technet.microsoft.com/en-us/library/cc879295(v=sql.110).aspx

msft 로지스틱 회귀분석은 msft 신경망 알고리즘의 한 형태이다. (신경망은 드릴쓰루가 안된다. )

나중에 좀 정리를 해야겠다. 저 표 읽을수 있다는게 참 다행이다. 하하하하하

모델을 만들고

분석하고 1

분석해서 2

예측한다.  

https://www.microsoft.com/en-us/server-cloud/products/sql-server/
http://www.gartner.com/technology/reprints.do?id=1-237UHKQ&ct=141016&st=sb

장점은 왠지 저런 기관에서 중요하게 생각하는 기능을 열심히 만들어 넣는듯?한 분위기이고 , 단점은 심각하다. 어플라이언스가 없고(DW제외), 분류는 엔터프라이즈 급 디비라고 하지만 사용자는 엔터프라이즈 어플리케이션에 잘 안쓴다. 2012부터 가격은 돌아버렸고, 아주 정말 아우~~~~ 장난치나...HA/DR 구현이 쉽지 않단다....음? 이건 먼소리지?

아래 표로는 좋아 보이지만 향 후 데이터베이스 시장은 엔터프라이즈 시장과 비 엔터프라이즈 시장으로 길을 나누어 갈 것이고 비 엔터프라이즈 시장은 mysql 이나 다른 무료 데이터베이스를 쓰면 충분하고 (all flash storage 쓰면 속도 아주 좋음 latch 는 설계 잘 하면 회피 할 수 있음) 캐시가 필요한 일반적인 곳은 윗단에 캐싱 레이어만 따로 구축하면 된다.

그나마, 데이터가 중요한 게임사는 가격 정책에 밀려 이미 다 떠나버렸고, mssql을 아직 게임사에 쓰면 개발사가 능력이 없는 것으로 보이고, 커뮤니티 같은 분야는 쓸 이유가 없고, 은행은 또 엔터프라이즈 느낌 안나는 mssql 잘 안쓴다 이말이군..

DB 배우는 사람은 그나마 미션 크리티컬한 곳에서 mssql 쓰는 곳에 취직을 해야 한다는 것인데, 이런곳에 고만 고만한 사람은 안 뽑는다. 과연 살아남을까? 이제 db를 처음 배운다면 잘 선택해야 할 듯 하다. 나는 끝까지 해서 메인프레임 관리자 처럼 되어봐야지 하하하하하하

Microsoft

Headquartered in Redmond, Washington, U.S., Microsoft markets its SQL Server DBMS for the operational DBMS market, as well as Microsoft Azure SQL Database (a database platform as a service) and Microsoft Azure Tables. Microsoft now has in-memory row-store technology for transactions in SQL Server 2014.

Strengths

• Market vision: Microsoft's market-leading vision consists of in-memory computing (SQL Server 2014 now has full transaction in-memory support), NoSQL (with a new document-store DBMS), cloud offerings (both cloud-only and hybrid cloud), use of analytics in transactions (HTAP) and support of mobility. Its vision for in-memory computing and putting the "cloud first" is ahead of its competitors.
• Strong execution: Microsoft SQL Server is an enterprisewide, mission-critical DBMS capable of competing with products from the other large DBMS vendors. Gartner's 2013 market share data shows Microsoft taking second place from IBM in terms of total DBMS revenue.
• Performance and support: Reference customers were very positive, with the performance of SQL Server, documentation, support, ease of installation and operation all rated highly. Only 7% reported problems with the DBMS overall.

Cautions

• Lack of an appliance: Microsoft still lacks an appliance for transactions (one comparable to its SQL Server Parallel Data Warehouse appliance), whereas its major competitors (IBM, Oracle and SAP) all offer one.
• Market image: Although SQL Server is an enterprise-class DBMS, Microsoft continues to struggle to dispel a perception of weakness in this area. Inquiries from Gartner clients demonstrate a continuing perception that SQL Server is not used for mission-critical enterprisewide applications — a view that inhibits wider use of SQL Server as a primary, enterprise-class DBMS.
• HA/DR and pricing issues: Reference customers again found the pricing model for SQL Server unacceptable (they gave it the lowest overall rating of any vendor in this Magic Quadrant) and blamed the price changes that came with SQL Server 2012. Microsoft also received one of the lowest overall scores for ease of implementing HA/DR.

연속된 값을 discretized 로 구간을 만들 수 있다.

아래와 같이 설정하면 되고, 연봉정보가 다 다를때, 나이대를 구분해 작업하고자 할 때 불연속 버전을 만들수 있다.

데이터 속성의 종류는 아래와 같고 그 특징을 이해해야 한다.

https://msdn.microsoft.com/ko-kr/library/ms174572.aspx

1. 선형회귀분석은 종속 변수와 독립 변수 간의 관계를 계산하고 이 관계를 예측에 사용

2. Y=aX+b

   를 구하는데, 모든 요소와 관련된 오류 수의 합계가 최소가 될 때까지 a와 b를 조정하여 회귀 수식을 구한다.

3. msft 의 선형회귀분석은 msft 의사 결정 트리의 변형이다. (트리의 분기가 없도록 조정한 것)

입력 연속된 숫자만 사용할 수 있다.

출력 연속된 숫자만 예측할 수 있다.

adventurework 에서 dimCustomer 의 평균연봉을 구하고자 한다면 다음과 같이 실습하면된다.

해당테이블에 나이가 없고 생일만 있으니 계산된 열을 이용해 추가한다.

dsv 에서 테이블에 계산된 컬럼을 추가한다. 일반적인 TSQL 이 먹는다.

선형 회귀분석 마이닝 구조와 모델을 만든다. 

모델링 플래그를 설정한다. (컬럼의 수동 추가 삭제 등의 경우 자동으로 설정되지 않는다.)

알고리즘 매개 변수를 확인한다.

선형 회귀분석의 경우 특별히 건드릴 변수가 없다.

마이닝 모델 뷰에서 Yearly Income 을 계산한 수식을 얻을 수 있다.

Yearly Income = 57,403.129+468.492*(Age-53.379)

만약 20살의 수입이 해당 모델로 학습한 경우 얼마가 되는지 계산해보면

마이닝 모델 예측에서는 더 쉽게 할 수 있다.

아래와 같이 설정하고 왼쪽 상단의 쿼리 버튼을 누르면 된다.

테이블을 넣어서 할 수도 있고, 단일 쿼리도 가능하고 dmx 쿼리를 바로 뽑아 낼 수도 있다.

 단일 쿼리를 구한것

단일 쿼리의 DMX 쿼리 확인  

쿼리분석기에서 데이터베이스에서 DMX 쿼리를 하면 똑같은 결과를 얻을 수 있다.

아래는 마이닝 파라메터를 호출한 경우이다.

 SELECT MINING_PARAMETERS
FROM $system.DMSCHEMA_MINING_MODELS
WHERE MODEL_NAME = 'LR_Customer_Income'

SELECT FLATTENED MODEL_NAME,
    (SELECT ATTRIBUTE_VALUE, VALUETYPE
     FROM NODE_DISTRIBUTION
     WHERE VALUETYPE =7)
AS t
FROM LR_Customer_Income.CONTENT

 SELECT FLATTENED NODE_DISTRIBUTION as t
FROM LR_Customer_Income.CONTENT

70% 샘플링을 해서 12939 건이 있고 연봉 평균값은 57403원이다.

계수는 (7번) 468 이고 평균 연령은 53세이다. 그래서 수식이

Yearly Income = 57,403.129+468.492*(Age-53.379) 이 나온것이다.

해석은 아래 링크를 보면 쉽게 할 수 있다.

https://msdn.microsoft.com/ko-kr/library/cc645906.aspx

이렇게 쉬운걸 ... 진작 공부해둘걸..... MSDN 만세~ !!!

IF OBJECT_ID('T_CALENDAR') IS NOT NULL
DROP TABLE T_CALENDAR
GO

CREATE TABLE T_CALENDAR
(IDX INT
,DATETIMEDATE DATETIME
,YYYYMMDD CHAR(8)
,IYEAR INT
,IMONTH INT
,IDAY INT
,IQUARTER INT
,IWEEK INT
,IWEEKDAY INT
,VWEEKDAY VARCHAR(10)
,HOLIDAY_YN INT
,HOLIDAY_NAME VARCHAR(100)
)
GO

INSERT INTO T_CALENDAR (
 IDX
,DATETIMEDATE
,YYYYMMDD
,IYEAR
,IMONTH
,IDAY
,IQUARTER
,IWEEK
,IWEEKDAY
,VWEEKDAY
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) IDX
 , CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1
 , CONVERT(VARCHAR(10), CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, 112)
 , DATEPART(YEAR, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(MONTH, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(DAY, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(QUARTER, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(WEEK, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , DATEPART(WEEKDAY, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
 , CASE DATEPART(WEEKDAY, CAST('20140101' AS DATETIME) + ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1)
  WHEN 1 THEN '일'
  WHEN 2 THEN '월'
  WHEN 3 THEN '화'
  WHEN 4 THEN '수'
  WHEN 5 THEN '목'
  WHEN 6 THEN '금'
  WHEN 7 THEN '토'
  ELSE NULL END
FROM SYSOBJECTS A
, SYSOBJECTS B
--, SYSOBJECTS C

UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20090101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20090125'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20090126'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20090127'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20090301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20090502'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20090505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20090606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20090815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20091002'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석,개천절' WHERE YYYYMMDD = '20091003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20091004'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20091225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20100101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20100213'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20100214'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20100215'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20100301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20100505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20100521'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20100606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20100815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20100921'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20100922'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20100923'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20101003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20101225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20110101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20110202'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20110203'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20110204'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20110301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20110505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20110510'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20110606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20110815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20110911'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20110912'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20110913'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20111003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20111225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20120101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20120122'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20120123'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20120124'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20120301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20120505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20120528'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20120606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20120815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20120929'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20120930'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20121001'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20121003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20121225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20130101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20130209'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20130210'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20130211'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20130301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20130505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20130517'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20130606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20130815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20130918'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20130919'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20130920'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20131003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20131225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20140101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20140130'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20140131'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20140201'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20140301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20140505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20140506'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20140606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20140815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20140907'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20140908'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20140909'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20141003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20141225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20150101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20150218'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20150219'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20150220'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20150301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20150505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20150525'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20150606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20150815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20150926'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20150927'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20150928'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20151003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20151225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20160101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20160207'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20160208'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20160209'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20160301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20160505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20160514'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20160606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20160815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20160914'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20160915'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20160916'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20161003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20161225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20170101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20170127'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20170128'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20170129'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20170301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20170503'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20170505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20170606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20170815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절,추석' WHERE YYYYMMDD = '20171003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20171004'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20171005'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20171225'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '신정' WHERE YYYYMMDD = '20180101'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20180215'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20180216'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '설날' WHERE YYYYMMDD = '20180217'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '삼일절' WHERE YYYYMMDD = '20180301'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '어린이날' WHERE YYYYMMDD = '20180505'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '석가탄신일' WHERE YYYYMMDD = '20180522'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '현충일' WHERE YYYYMMDD = '20180606'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '광복절' WHERE YYYYMMDD = '20180815'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20180923'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20180924'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '추석' WHERE YYYYMMDD = '20180925'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '개천절' WHERE YYYYMMDD = '20181003'
UPDATE DBO.T_CALENDAR SET HOLIDAY_YN = 1 , HOLIDAY_NAME = '성탄절' WHERE YYYYMMDD = '20181225'

UPDATE T_CALENDAR
SET HOLIDAY_YN = 1
WHERE VWEEKDAY IN ('토','일')

UPDATE T_CALENDAR
SET HOLIDAY_YN = 0
, HOLIDAY_NAME = ''
WHERE HOLIDAY_NAME IS NULL

DELETE T_CALENDAR WHERE YYYYMMDD > '20181225'
GO

SELECT * FROM T_CALENDAR