質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

1回答

1486閲覧

プロシージャの処理内容がわかりません。②

KINGFROG

総合スコア27

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

0グッド

0クリップ

投稿2016/10/16 07:07

一時テーブルから本テーブルへデータを移行するプロシージャで、処理方法が理解できません。
特に論理削除、更新、追加の処理で、どういったデータが一時テーブルから移行されると実行されるのかが知りたいです。(コードが長いため、①、②と分けて書いています)
どなたかご教示頂ければと思います。
宜しくお願い致します。

-- 5. ワンタイムマスタ更新
UPDATE dbo.MST_W_SHOP_SHIPMENT
SET SHOP_SHIPMENT_AGENCY_CD = T1.ONETMCSTM_CD
, EFFECT_FLG = 1
, MST_HANDLING_COMPANY_ID = '1'
, SALES_ORGANIZATION = T1.VKORG
, DISTRIBUTION_CHANNEL = T1.LOGI_CHANEL_CD
, PRODUCT_DIVISION = T1.SPART
, CLIENT_ACCOUNT_GROUP = '0'
, SEARCH_CONDITION1 = T1.KANA_NM
, NAME = T1.SHORT_NM
, POST_CODE = T1.POST_CD_SAP
, COUNTRY_CODE = T1.COUNTRY
, GEOGRAPHICAL_NAME = ' '
, LANGUAGE = 'J'
, TEL1 = T1.TEL
, FAX = T1.FAX
, SHIPPING_CONDITION = T1.WAERS
, COUNTRY_DISPATCH = T1.KALKS
, TAX_CATEGORY = 'MWST'
, CLIENT_TAX_GROUPING = T1.TAXKD
, CLIENT_GROUP1 = T1.KVGR1
, DELIVERY_ZONE = T1.SHIP_AREA
, RECEIVED_BLOCK = ' '
, SHIPPING_BLOCK = ' '
, PREFECTURE_CD = '13'
, SALES_PROMOTION_FLG = ' '
, BRANCH_OFFICE_CD = T1.AREA_CD
, REGION_ID = T1.AREA_CD
, UPDATE_DATE = @dUpdate_Date
, UPDATE_USER_ID = @vUpdate_User_Id
, UPDATE_PRG_ID = @vUpdate_Prg_Id
FROM #tTmp_MARSIF_TMP AS T1
WHERE NOT EXISTS (SELECT *
FROM #tTmp_DUPLICAT
WHERE #tTmp_DUPLICAT.LOGI_ONETMCSTM_CD = T1.LOGI_ONETMCSTM_CD)
AND MST_W_SHOP_SHIPMENT.SHOP_SHIPMENT_CD = T1.LOGI_ONETMCSTM_CD
AND (MST_W_SHOP_SHIPMENT.SHOP_SHIPMENT_AGENCY_CD <> T1.ONETMCSTM_CD
OR (MST_W_SHOP_SHIPMENT.SHOP_SHIPMENT_AGENCY_CD IS NULL AND T1.ONETMCSTM_CD IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.SHOP_SHIPMENT_AGENCY_CD IS NOT NULL AND T1.ONETMCSTM_CD IS NULL)
OR MST_W_SHOP_SHIPMENT.EFFECT_FLG <> 1
OR ISNULL(MST_W_SHOP_SHIPMENT.MST_HANDLING_COMPANY_ID, '') <> '1'
OR MST_W_SHOP_SHIPMENT.SALES_ORGANIZATION <> T1.VKORG
OR (MST_W_SHOP_SHIPMENT.SALES_ORGANIZATION IS NULL AND T1.VKORG IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.SALES_ORGANIZATION IS NOT NULL AND T1.VKORG IS NULL)
OR MST_W_SHOP_SHIPMENT.DISTRIBUTION_CHANNEL <> T1.LOGI_CHANEL_CD
OR (MST_W_SHOP_SHIPMENT.DISTRIBUTION_CHANNEL IS NULL AND T1.LOGI_CHANEL_CD IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.DISTRIBUTION_CHANNEL IS NOT NULL AND T1.LOGI_CHANEL_CD IS NULL)
OR MST_W_SHOP_SHIPMENT.PRODUCT_DIVISION <> T1.SPART
OR (MST_W_SHOP_SHIPMENT.PRODUCT_DIVISION IS NULL AND T1.SPART IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.PRODUCT_DIVISION IS NOT NULL AND T1.SPART IS NULL)
OR ISNULL(MST_W_SHOP_SHIPMENT.CLIENT_ACCOUNT_GROUP, '') <> '0'
OR MST_W_SHOP_SHIPMENT.SEARCH_CONDITION1 <> T1.KANA_NM
OR (MST_W_SHOP_SHIPMENT.SEARCH_CONDITION1 IS NULL AND T1.KANA_NM IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.SEARCH_CONDITION1 IS NOT NULL AND T1.KANA_NM IS NULL)
OR MST_W_SHOP_SHIPMENT.NAME <> T1.SHORT_NM
OR (MST_W_SHOP_SHIPMENT.NAME IS NULL AND T1.SHORT_NM IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.NAME IS NOT NULL AND T1.SHORT_NM IS NULL)
OR MST_W_SHOP_SHIPMENT.POST_CODE <> T1.POST_CD_SAP
OR MST_W_SHOP_SHIPMENT.COUNTRY_CODE <> T1.COUNTRY
OR (MST_W_SHOP_SHIPMENT.COUNTRY_CODE IS NULL AND T1.COUNTRY IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.COUNTRY_CODE IS NOT NULL AND T1.COUNTRY IS NULL)
OR ISNULL(MST_W_SHOP_SHIPMENT.GEOGRAPHICAL_NAME, '') <> ' '
OR ISNULL(MST_W_SHOP_SHIPMENT.LANGUAGE, '') <> 'J'
OR MST_W_SHOP_SHIPMENT.TEL1 <> T1.TEL
OR (MST_W_SHOP_SHIPMENT.TEL1 IS NULL AND T1.TEL IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.TEL1 IS NOT NULL AND T1.TEL IS NULL)
OR MST_W_SHOP_SHIPMENT.FAX <> T1.FAX
OR (MST_W_SHOP_SHIPMENT.FAX IS NULL AND T1.FAX IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.FAX IS NOT NULL AND T1.FAX IS NULL)
OR MST_W_SHOP_SHIPMENT.SHIPPING_CONDITION <> T1.WAERS
OR (MST_W_SHOP_SHIPMENT.SHIPPING_CONDITION IS NULL AND T1.WAERS IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.SHIPPING_CONDITION IS NOT NULL AND T1.WAERS IS NULL)
OR MST_W_SHOP_SHIPMENT.COUNTRY_DISPATCH <> T1.KALKS
OR (MST_W_SHOP_SHIPMENT.COUNTRY_DISPATCH IS NULL AND T1.KALKS IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.COUNTRY_DISPATCH IS NOT NULL AND T1.KALKS IS NULL)
OR ISNULL(MST_W_SHOP_SHIPMENT.TAX_CATEGORY, '') <> 'MWST'
OR MST_W_SHOP_SHIPMENT.CLIENT_TAX_GROUPING <> T1.TAXKD
OR (MST_W_SHOP_SHIPMENT.CLIENT_TAX_GROUPING IS NULL AND T1.TAXKD IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.CLIENT_TAX_GROUPING IS NOT NULL AND T1.TAXKD IS NULL)
OR MST_W_SHOP_SHIPMENT.CLIENT_GROUP1 <> T1.KVGR1
OR (MST_W_SHOP_SHIPMENT.CLIENT_GROUP1 IS NULL AND T1.KVGR1 IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.CLIENT_GROUP1 IS NOT NULL AND T1.KVGR1 IS NULL)
OR MST_W_SHOP_SHIPMENT.DELIVERY_ZONE <> T1.SHIP_AREA
OR (MST_W_SHOP_SHIPMENT.DELIVERY_ZONE IS NULL AND T1.SHIP_AREA IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.DELIVERY_ZONE IS NOT NULL AND T1.SHIP_AREA IS NULL)
OR ISNULL(MST_W_SHOP_SHIPMENT.RECEIVED_BLOCK, '') <> ' '
OR ISNULL(MST_W_SHOP_SHIPMENT.SHIPPING_BLOCK, '') <> ' '
OR ISNULL(MST_W_SHOP_SHIPMENT.PREFECTURE_CD, '') <> '13'
OR ISNULL(MST_W_SHOP_SHIPMENT.SALES_PROMOTION_FLG, '') <> ' '
OR MST_W_SHOP_SHIPMENT.BRANCH_OFFICE_CD <> T1.AREA_CD
OR (MST_W_SHOP_SHIPMENT.BRANCH_OFFICE_CD IS NULL AND T1.AREA_CD IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.BRANCH_OFFICE_CD IS NOT NULL AND T1.AREA_CD IS NULL)
OR MST_W_SHOP_SHIPMENT.REGION_ID <> T1.AREA_CD
OR (MST_W_SHOP_SHIPMENT.REGION_ID IS NULL AND T1.AREA_CD IS NOT NULL)
OR (MST_W_SHOP_SHIPMENT.REGION_ID IS NOT NULL AND T1.AREA_CD IS NULL)
)
-- 6. ワンタイムマスタ追加
INSERT INTO dbo.MST_W_SHOP_SHIPMENT
(SHOP_SHIPMENT_CD
, SHOP_SHIPMENT_AGENCY_CD
, EFFECT_FLG
, MST_HANDLING_COMPANY_ID
, SALES_ORGANIZATION
, DISTRIBUTION_CHANNEL
, PRODUCT_DIVISION
, CLIENT_ACCOUNT_GROUP
, SEARCH_CONDITION1
, NAME
, POST_CODE
, COUNTRY_CODE
, GEOGRAPHICAL_NAME
, LANGUAGE
, TEL1
, FAX
, SHIPPING_CONDITION
, COUNTRY_DISPATCH
, TAX_CATEGORY
, CLIENT_TAX_GROUPING
, CLIENT_GROUP1
, DELIVERY_ZONE
, RECEIVED_BLOCK
, SHIPPING_BLOCK
, PREFECTURE_CD
, SALES_PROMOTION_FLG
, BRANCH_OFFICE_CD
, REGION_ID
, CREATE_DATE
, CREATE_USER_ID
, CREATE_PRG_ID
, UPDATE_DATE
, UPDATE_USER_ID
, UPDATE_PRG_ID
)
SELECT T1.LOGI_ONETMCSTM_CD
, T1.ONETMCSTM_CD
, 1
, '1'
, T1.VKORG
, T1.LOGI_CHANEL_CD
, T1.SPART
, '0'
, T1.KANA_NM
, T1.SHORT_NM
, T1.POST_CD_SAP
, T1.COUNTRY
, ' '
, 'J'
, T1.TEL
, T1.FAX
, T1.WAERS
, T1.KALKS
, 'MWST'
, T1.TAXKD
, T1.KVGR1
, T1.SHIP_AREA
, ' '
, ' '
, '13'
, ' '
, T1.AREA_CD
, T1.AREA_CD
, @dUpdate_Date
, @vUpdate_User_Id
, @vUpdate_Prg_Id
, @dUpdate_Date
, @vUpdate_User_Id
, @vUpdate_Prg_Id
FROM #tTmp_MARSIF_TMP AS T1
WHERE NOT EXISTS (SELECT *
FROM #tTmp_DUPLICAT
WHERE #tTmp_DUPLICAT.LOGI_ONETMCSTM_CD = T1.LOGI_ONETMCSTM_CD)
AND NOT EXISTS (SELECT *
FROM MST_W_SHOP_SHIPMENT
WHERE MST_W_SHOP_SHIPMENT.SHOP_SHIPMENT_CD = T1.LOGI_ONETMCSTM_CD)
; COMMIT TRANSACTION -- コミット実行
-- 7.正常完了ログ出力
EXEC sc_090_000 '0','sp_10_100(ワンタイムマスタ登録)','SUCCESS'
Return 0 -- 正常
End Try
--例外処理
Begin Catch
IF (@iIs_TrnStart = 1)
BEGIN
ROLLBACK TRANSACTION -- ロールバック実行
END

SET @ErrMessage = '例外が発生しました。' EXEC sc_090_000 '-1','sp_10_100(ワンタイムマスタ登録)',@ErrMessage --エラーログ出力 Return -1 -- エラー

End Catch
SET ANSI_NULLS ON
GO

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

自己解決

なんとか自己解決できました。

投稿2016/11/23 02:31

KINGFROG

総合スコア27

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問