데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

CREATE PROC LOTRO_SRC_ODS_V_LOTROObjectCharacterHistory
AS

TRUNCATE TABLE DTS_SVR.DBO.LOTRO_V_LOTROObjectCharacterHistory

DECLARE
   @WORKDATE VARCHAR(100) -- 작업일
, @DATE1 VARCHAR(100)  -- 어제 6 (작업일 기준)
, @DATE2 VARCHAR(100)  -- 오늘 6
, @DATE3 VARCHAR(100)  -- 어제 8
, @DATE4 VARCHAR(100)  -- 오늘 8
, @TIME VARCHAR(100)  -- 기준시간
, @SQL VARCHAR(8000)  -- SQL 문장

--날짜가 바뀌면 이부분만 바꾸면 됩니다.
SET @WORKDATE = CONVERT(CHAR(8),GETDATE()-1,112)

SELECT
@WORKDATE = @WORKDATE
, @DATE1 = RIGHT(CONVERT(CHAR(8), DATEADD(DAY,0, @WORKDATE), 112),6)
, @DATE2 = RIGHT(CONVERT(CHAR(8), DATEADD(DAY,1, @WORKDATE), 112),6)
, @DATE3 = RIGHT(CONVERT(CHAR(8), DATEADD(DAY,0, @WORKDATE), 112),8)
, @DATE4 = RIGHT(CONVERT(CHAR(8), DATEADD(DAY,1, @WORKDATE), 112),8)
, @TIME = '03:00:00.000' -- 시간이 바뀌면 이부분만 바뀌면 됩니다.
, @SQL = ''
-- 디비그
-- SELECT @WORKDATE, @DATE1, @DATE2, @DATE3, @DATE4, @SQL, @TIME

DECLARE
@SQL2 NVARCHAR(4000)
, @VARI NVARCHAR(4000)
, @ObjectHistoryID BIGINT
, @ObjectHistoryIDVarChar VARCHAR(100)

-- GRS1

SET @SQL2 = 'SELECT TOP 1 @ObjectHistoryID = [ObjectHistoryID] FROM OPENROWSET
  (''SQLOLEDB'',''192.168.0.21'';''userid'';''password'',
   ''
    SELECT
     TOP 1 ObjectHistoryID
    FROM
     LOTROGRS001.DBO.V_LOTROObjectCharacterHistory WITH (NOLOCK)
    WHERE
     CreateDate >= '''''+@DATE3+' '+@TIME+'''''
     AND CreateDate < '''''+@DATE4+' '+@TIME+'''''
     AND ObjectType = 10
    ORDER BY
     CreateDate ASC
   ''
  )'

SET @VARI = '@ObjectHistoryID BIGINT OUT'
EXEC SP_EXECUTESQL @SQL2, @VARI, @ObjectHistoryID OUT
SET @ObjectHistoryIDVarChar = ISNULL(@ObjectHistoryID,0)

SET @SQL =
'
INSERT INTO DTS_SVR.DBO.LOTRO_V_LOTROObjectCharacterHistory WITH(TABLOCK)
SELECT * FROM OPENROWSET(''SQLOLEDB'',''192.168.0.21'';''userid'';''password'',
''
  SELECT
   1 WORLDID
   , [ObjectHistoryID], [ParentHistoryID], [ObjectID], [ParentID], [ObjectType]
   , [CreateDate], [EventID], [WorldName], [SubscriptionName], [AccountName]
   , [GameCreateTime], [Name], [IsAdmin], [Species], [Class]
   , [Level], [EarnedXP], [TerritoryID], [AreaID], [DungeonID]
   , [GuildID], [GuildRank], [AProfessionID], [AProficiencyLevel], [AMasteryLevel]
   , [BProfessionID], [BProficiencyLevel], [BMasteryLevel], [CProfessionID], [CProficiencyLevel]
   , [CMasteryLevel], [Currency], [IsMonsterPlayer], [ConjunctionSkillType], [PvpRatingPoints]
   , [GloryPoints], [GloryRank], [Nationality], [IsDeleted], [MPPercent]
   , [DestinyPoints], [GarnishRemaining], '''''+@DATE3+'''''
  FROM
   LOTROGRS001.DBO.V_LOTROObjectCharacterHistory WITH (NOLOCK)
  WHERE
     CreateDate >= '''''+@DATE3+' '+@TIME+'''''
     AND CreateDate < '''''+@DATE4+' '+@TIME+'''''
     AND ObjectType = 10
   AND ObjectHistoryId >= '+@ObjectHistoryIDVarChar+'
''
)
'

EXEC (@SQL)

go

위 프로시져를 분석해보시면 좋을것 같습니다.

석이님이 2008-05-27 14:30에 작성한 댓글입니다. Edit Ｘ