やりたい事は簡単な事なのですが、マイクロソフトアクセス（以下Accessに略）を使ってSqlServerのテーブルを更新しようとすると重くてODBCが接続エラーになったり、反応が返ってこない状態になってしまうような事が発生しました。
SqlServerがExpressなので何とも言えない所があるのですが、何ともモヤモヤする不具合になります。
そこで、仕方なく、SqlServerの方で処理をさせるような感じになり、ストアドを作成してみた訳です。
ココがポイント
テーブルのデータを移動させたい！
という簡単な内容なのですが、ストアドでハマったので備忘録としました。
注意ポイント
サンプル例として、
コピー親テーブル[order]、コピー親元テーブル[order_end]
リレーションで親テーブルと組んでいる子テーブルが、
コピー元子テーブル[trader_ag]、コピー元子テーブル[trader_ag_end]
として解説を進めていきます。
対象レコードをコピー（別テーブルに追加）
テーブル間でのレコード移動なのですが、まずは、対象レコードを移動先テーブルにコピーすることから始まりました。
ココに注意
コピーと言っても移動先テーブルへのレコード追加ですね
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[order_end_job]
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @DATETIME DATETIME;
DECLARE @KN VARCHAR(50);
--２ヶ月前に設定
SET @DATETIME = DATEADD(MONTH,-2,GETDATE());
SET @KN = CONVERT(VARCHAR(50), @DATETIME, 111);
INSERT INTO order_end ( NO, flag, mgflag, hoku_btn1, hoku_btn2, confirmed, reception_date, reception_time, shipper, pu_location, pul_pref, pul_city, pul_ad1, pul_ad2, pu_tel, pu_date, pu_time, delivery_date, delivery_time, vehicle, total, bill, payment, pu_destination_pre, pu_destination, delivery_name_pre, delivery_name, remarks, date_no, approved, bp_no, inquiry_no, request_store, trader, trader_payment, office_remarks, vehicle_info, tanto, tanto_phone, pu_notes, deli_company_name, deli_add1, deli_add2, deli_tanto, deli_tel, deli_phone, deli_notes )
SELECT [order].[NO], [order].[flag], [order].[mgflag], [order].[hoku_btn1], [order].[hoku_btn2], [order].[confirmed], [order].[reception_date], [order].[reception_time], [order].[shipper], [order].[pu_location], [order].[pul_pref], [order].[pul_city], [order].[pul_ad1], [order].[pul_ad2], [order].[pu_tel], [order].[pu_date], [order].[pu_time], [order].[delivery_date], [order].[delivery_time], [order].[vehicle], [order].[total], [order].[bill], [order].[payment], [order].[pu_destination_pre], [order].[pu_destination], [order].[delivery_name_pre], [order].[delivery_name], [order].[remarks], [order].[date_no], [order].[approved], [order].[bp_no], [order].[inquiry_no], [order].[request_store], [order].[trader], [order].[trader_payment], [order].[office_remarks], [order].[vehicle_info], [order].[tanto], [order].[tanto_phone], [order].[pu_notes], [order].[deli_company_name], [order].[deli_add1], [order].[deli_add2], [order].[deli_tanto], [order].[deli_tel], [order].[deli_phone], [order].[deli_notes]
FROM [order]
WHERE LTRIM(RTRIM([order].[delivery_date] )) <> '' and [order].[delivery_date] IS NOT NULL and [order].[delivery_date] < @KN
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_NUMBER()
END CATCH
RETURN 0フィールドが多いのでごちゃごちゃとしていますが、フィールドを減らせばシンプルなコードになります。
軽くはまったのが、「[」などのカッコを付けるのか？つけなくていいのか？問題ですね。上のように感じが最終系として動きましたので、こんな感じってことで会得するしかないですね。
それから、最大にハマったのが、WHEREでのデータを絞り込むコードになります。
本日の日付より２ヶ月前以前のデータを別テーブルに追加するという感じなのですが、なかなか絞り込みができません。
ココに注意
重要だったのは、SqlServerからのデータがテキスト形式だったことを見逃した事
そこで、本日の日付から２ヶ月前の日付を下記の様なコードにして、
DATEADD(MONTH,-2,GETDATE())
この日付コードをコンバートでテキストにしました。
CONVERT(VARCHAR(50), @DATETIME, 111)
※１１１というのは、「YYYY/MM/DD」の形式にする番号になります。参考リンク
２ヶ月前のテキスト日付を@KNという変数にして、
[order].[delivery_date] < @KN
データを２ヶ月前の物だけに絞る事に成功しました。
複数条件でNULLにハマった！
なんとか日付でのコピーに成功したのですが、次の段階として、空白フィールドは除外したいのにハマりました。
日付が２ヶ月以前のデータであり、ただし、空欄のデータは除くという事をやりたかったのです。
[order].[delivery_date] IS NOT NULL and [order].[delivery_date] < @KN
これでいいんじゃないの？って思いましたが、データを見ると、空欄もコピーされてしまっていました。
ココに注意
結論は、「””」はNULLじゃないってことですかね。
LTRIM(RTRIM([order].[delivery_date] )) <> ''
上のコードもandに加えることで、やっと目的のデータが別テーブルに追加することができました。
リレーション組んでいる子テーブルデータも移動
ちょっと複雑なのですが、コピー元テーブルには、リレーションで組んでいる子テーブルがぶら下がっています。
この子テーブルデータも、別テーブルに移動させたいのです。もちろん、親テーブルでリレーションされたデータのみとなります。
既に、親テーブル[order]からコピー先親テーブル[order_end]にコピーされているので、コピー先親テーブルを元に、子テーブルデータをコピーしました。
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[trader_ag_end_job]
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO trader_ag_end ( od_no, trader1, vehicle1, total1, assistant1, trader1_payment, trader2, vehicle2, total2, assistant2, trader2_payment, trader3, vehicle3, total3, assistant3, trader3_payment, trader4, vehicle4, total4, assistant4, trader4_payment, trader5, vehicle5, total5, assistant5, trader5_payment, all_total, all_assistant, notes1, deli_notes1, notes2, deli_notes2, notes3, deli_notes3, notes4, deli_notes4, notes5, deli_notes5 )
SELECT [trader_ag].[od_no], [trader_ag].[trader1], [trader_ag].[vehicle1], [trader_ag].[total1], [trader_ag].[assistant1], [trader_ag].[trader1_payment], [trader_ag].[trader2], [trader_ag].[vehicle2], [trader_ag].[total2], [trader_ag].[assistant2], [trader_ag].[trader2_payment], [trader_ag].[trader3], [trader_ag].[vehicle3], [trader_ag].[total3], [trader_ag].[assistant3], [trader_ag].[trader3_payment], [trader_ag].[trader4], [trader_ag].[vehicle4], [trader_ag].[total4], [trader_ag].[assistant4], [trader_ag].[trader4_payment], [trader_ag].[trader5], [trader_ag].[vehicle5], [trader_ag].[total5], [trader_ag].[assistant5], [trader_ag].[trader5_payment], [trader_ag].[all_total], [trader_ag].[all_assistant], [trader_ag].[notes1], [trader_ag].[deli_notes1], [trader_ag].[notes2], [trader_ag].[deli_notes2], [trader_ag].[notes3], [trader_ag].[deli_notes3], [trader_ag].[notes4], [trader_ag].[deli_notes4], [trader_ag].[notes5], [trader_ag].[deli_notes5]
FROM [trader_ag] INNER JOIN order_end ON [trader_ag].[od_no] = order_end.[NO];
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_NUMBER()
END CATCH
RETURN 0これもフィールドが多いのでごちゃごちゃしていますが、ポイントは、リレーションをしている下記のコードですね。
FROM [trader_ag] INNER JOIN order_end ON [trader_ag].[od_no] = order_end.[NO];
移動元テーブルからデータ削除
SqlServerが非力じゃなければ、データ削除することは無いのですが、自分の環境では非力すぎるので作業テーブルからはデータを削除することが重要なのです。
ということで、コピー元親テーブルから、コピー済データを削除しました。
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[order_end_del]
AS
BEGIN TRY
BEGIN TRANSACTION
DECLARE @DATETIME DATETIME;
DECLARE @KN VARCHAR(50);
--２ヶ月前に設定
SET @DATETIME = DATEADD(MONTH,-2,GETDATE());
SET @KN = CONVERT(VARCHAR(50), @DATETIME, 111);
DELETE FROM [order]
WHERE LTRIM(RTRIM([order].[delivery_date] )) <> '' and [order].[delivery_date] IS NOT NULL and [order].[delivery_date] < @KN
--trader_agの方は、リレーションシップで自動で消されている
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RETURN ERROR_NUMBER()
END CATCH
RETURN 0ココがポイント
同じ条件でWHEREした物を削除すればいい
だけなので、上のコードのようにシンプルになりました。
あとコメントアウトして説明文を追加していますが、コピー元子テーブルの方は、親テーブルから消すとリレーションで自動で消されていました。
もちろんリレーションを組んでないとだめなので注意してください。
他にハマった事
ストアドに慣れてないとちょっとしたことで無駄な時間を費やしてしまいますが、
ココに注意
なかなかコピー先にレコードが追加されなかったので悩みました。
特にエラーも出ずに、WHRER条件もあっているのにレコードが追加されないのです。
そして、無駄な時間を費やす事数時間。
さらに詳しく
コピー先のフィールドの一つがオートナンバーになっていました。
コピー元テーブルから複製したので、コピー先もオートナンバーが引き継がれたようです。
オートナンバーにも、値を代入しようとしてレコードの追加が出来なかったようです。
わかりやすいエラーでも出してくれればいいのですが、処理０としか出ないので時間がかかってしまいました。（泣）
慣れてないのもありますが、SqlServerでの開発は情報が無さすぎます。本当に疲れます。