[MSSQL] OPENQUERY 예제 쿼리

■ 변수선언

DECLARE @P_CD_COMPANY			NVARCHAR(7)='TEST',
		@P_ID_INSERT				NVARCHAR(15) = '1234',
		@P_DT_START					NVARCHAR(8) = '20220801',
		@P_DT_END					NVARCHAR(8) = '20220831',

■ UPDATE

SET	@V_SQL	=	'
				UPDATE A
				SET A.KIDS_BK_CD	= B.KIDS_BK_CD	,
					A.IN_QTY		= B.IN_QTY		,
					A.IN_AMT		= B.IN_AMT		,
					A.ID_UPDATE		= ''' + @P_ID_INSERT + '''		,
					A.DTS_UPDATE    = ''' + @V_SYSDATE + '''
				FROM CZ_KIDV_INPUT A WITH(NOLOCK)
					INNER JOIN OPENQUERY(MWMS, '' SELECT * FROM KIDV_INPUT WHERE TAX_NO=''''0443'''' AND SUBL_DATE BETWEEN ''''' + @P_DT_START + ''''' AND ''''' + @P_DT_END + ''''' '') B
					ON B.SUBL_DATE=A.SUBL_DATE AND B.TAX_NO=A.TAX_NO AND B.SUBL_NO=A.SUBL_NO AND B.SUBL_SEQ=A.SUBL_SEQ AND B.SUBL_GB = A.SUBL_GB
				WHERE ((B.KIDS_BK_CD <> A.KIDS_BK_CD) OR (B.IN_QTY	<> A.IN_QTY) OR (B.IN_AMT <> A.IN_AMT))
				AND A.TAX_NO = ''0443''
				AND A.SUBL_DATE BETWEEN ''' + @P_DT_START + ''' AND ''' +  @P_DT_END + '''
				'
EXEC (@V_SQL)

■ INSERT

SET	@V_SQL	=	'
				INSERT INTO CZ_KIDV_INPUT
				(
					SUBL_DATE, TAX_NO, SUBL_NO, SUBL_SEQ, SUBL_GB, SUBL_GB_NM, BK_CD, BK_NM, KIDS_BK_CD, IN_QTY, IN_AMT, PO_NO, REMARK, INS_EMP, INS_DATE, ID_INSERT, DTS_INSERT
				)
				SELECT SUBL_DATE, TAX_NO, SUBL_NO, SUBL_SEQ, SUBL_GB, SUBL_GB_NM, BK_CD, BK_NM, KIDS_BK_CD, IN_QTY, IN_AMT, PO_NO, REMARK, INS_EMP, INS_DATE, ''' + @P_ID_INSERT + ''', ''' + @V_SYSDATE + '''
				FROM OPENQUERY(MWMS, '' SELECT * FROM KIDV_INPUT WHERE TAX_NO=''''0443'''' AND SUBL_DATE BETWEEN ''''' + @P_DT_START + ''''' AND ''''' + @P_DT_END + ''''' '') A 
				WHERE	NOT EXISTS (SELECT 1 FROM CZ_KIDV_INPUT Z WITH (NOLOCK)
									WHERE	Z.SUBL_DATE		= A.SUBL_DATE
									AND		Z.TAX_NO		= A.TAX_NO
									AND		Z.SUBL_NO		= A.SUBL_NO
									AND		Z.SUBL_SEQ		= A.SUBL_SEQ
									AND		Z.SUBL_GB		= A.SUBL_GB)
				AND A.SUBL_DATE BETWEEN ''' + @P_DT_START + ''' AND ''' +  @P_DT_END + '''
				'
EXEC (@V_SQL)

■ DELETE

SET	@V_SQL	=	'
				DELETE FROM CZ_KIDV_INPUT
				FROM CZ_KIDV_INPUT A WITH(NOLOCK)
				WHERE	NOT EXISTS (SELECT 1 FROM OPENQUERY(MWMS, '' SELECT * FROM KIDV_INPUT WHERE TAX_NO=''''0443'''' AND SUBL_DATE BETWEEN ''''' + @P_DT_START + ''''' AND ''''' + @P_DT_END + ''''' '') Z
									WHERE	Z.SUBL_DATE		= A.SUBL_DATE
									AND		Z.TAX_NO		= A.TAX_NO
									AND		Z.SUBL_NO		= A.SUBL_NO
									AND		Z.SUBL_SEQ		= A.SUBL_SEQ
									AND		Z.SUBL_GB		= A.SUBL_GB)
				AND A.TAX_NO = ''0443''
				AND A.SUBL_DATE BETWEEN ''' + @P_DT_START + ''' AND ''' +  @P_DT_END + '''
				'
EXEC (@V_SQL)

 

---

■ 개인적인 팁
 - 파라미터 : openquery 안에서는 ''''' - 5개
                     밖에서는 '' - 3개
 - 문자열 : openquery 안에서는 '''' - 4개
                 밖에서는 '' - 2개

           

끝~~