보미네

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