一括insertできない件

失敗の原因はトリガーでした。
トリガー内の from inserted のinsertedを
レコードと思っていたのが原因です。
よく考えればfromだから次はテーブルですね。
さらにnull値更新のコード簡素化のために関数作ってパラメータに
from insertedを入れ込んだのでますますいけませんでした。
関数をやめて長々とコード書いて最後に
where no in (select no from inserted)
で解決
inserted deleted はテーブルと肝に銘じよう

format

SQLServerでもfromatと以前UPしましたが
馴れているVBAのformatとは大分違っている事に気がつく
今日2017/10/27の日付で
select format(getdate(),’D’) 2017年10月27日
select format(getdate(),’d’) 2017/10/27
select format(getdate(),’m’) 10月27日
select format(getdate(),’M’) 10月27日
select format(getdate(),’y’) 2017年10月
select format(getdate(),’Y’) 2017年10月
select format(getdate(),’DD’) DD
select format(getdate(),’dd’) 27
select format(getdate(),’MM’) 10
select format(getdate(),’mm’) 40(現在時の分)
select format(getdate(),’YY’) YY
select format(getdate(),’yy’) 17
select format(getdate(),’yy/mm/dd’) 17/01/27 01は現在時刻の分
select format(getdate(),’yy/MM/dd’) 17/10/27

最後のはどうにも変
VBAではどうかというと
debug.Print format(now,”D”) 27
debug.Print format(now,”d”) 27
debug.Print format(now,”m”) 10
debug.Print format(now,”M”) 10
debug.Print format(now,”Y”) 300 300はなんだかわからない
debug.Print format(now,”y”) 300
debug.Print format(now,”DD”) 27
debug.Print format(now,”dd”) 27
debug.Print format(now,”mm”) 10
debug.Print format(now,”MM”) 10
debug.Print format(now,”yy”) 17
debug.Print format(now,”YY”) 17
debug.Print format(now,”yy/mm/dd”) 17/10/27
debug.Print format(now,”yy/MM/dd”) 17/10/27

かなり違う
ではVBAで分はというとn
select format(getdate(),’MM’) = debug.Print format(now,”nn”)

MERGE

二つのテーブルを比較し同じレコードが
あればupdate なければinsert
VBAでならレコードセット取っていろいろとやってれば
できるんでしょうが長くなるしAccess上でとなると時間もかかりそうな
のでSQLServerでしたい

となるとMERGEという事になるらしいです。
クエリ一文で済みます、たぶん。

merge into dbo.TableA as A
using dbo.TableB as B
on
(A.ID=B.ID)
when matched then
update set
name=B.name,
kana=B.kana,
Email=B.Email
when not matched then
insert(id,name,kana,email)
values(
B.ID,
B.name,
B.kana,
B.Email
)
;

肝はIDが一意であるという事ですがDBで一意となると自然増加のIDに
なっている事が多いでしょうからTableAの自然増加は外す
それとやはりSQLServerとMySQL間でMERGEは無理なのでテーブルを
移行させておく準備が必要

updateとinsertを同時に行うのでupsertともいうらしい

SSMSでテーブル変更

はオプション変更すればGUIで可能だが非推奨らしい。
ではどうすれば良いかというと「クエリを発行しなさい」
らしい

alter table 滅多に使いませんからメモです。
結局変更の履歴を記録したいという事かな。
かなり面倒くさい。やるかやらぬか別にしてとりあえず・・

注 SSMS=SQL Server Management Studio

・表に列を追加する

ALTER TABLE テーブル名 ADD

列名1 VARCHAR (10) DEFAULT ” NOT NULL

,列名2 VARCHAR (10) DEFAULT ” NOT NULL

・表の列定義を変更する

ALTER TABLE テーブル名 ALTER COLUMN 変更する列名 データ型

例:ALTER TABLE TABLE_A ALTER COLUMN COLUMN_1 VARCHAR(200)

・表の列を削除する

ALTER TABLE テーブル名 DROP COLUMN

列名1,列名2

・表の名前を変更する

EXEC sp_rename ‘現在のテーブル名’,’変更するテーブル名’,’OBJECT’
・表の列名を変更する

EXEC sp_rename ‘テーブル名.現在の列名’,’変更する列名’,’COLUMN’

http://www.projectgroup.info/tips/SQLServer/SQL/SQL000005.html

SQLserver とクライアントとODBC

ACCESSフロントエンド SQLserverバックエンドDBでDBアプリが開発できんかなあと奮闘中
形だけ整ってきたのでアプリが膨れ上がる前にローカルで開発したものをクライアントでも
動かすと・・
まず戸惑ったのがODBC 
SQL Server ODBC,ODBC Driver 13 for SQLserver,SQL server native client 
3択になってる?
Mysqlは少しだけかじったけど一択なんだけど・・
調べると再頒布ならSQL server native clientが良さげとGoogle先生がおっしゃるが
なぜか競合エラー 他のユーザーが同時に更新を・・で挫折
やむなく、とりあえず、SQL serverを選択 こちらでは何故か起きない
windows認証でSQLserverログインの場合
どのコネクターでもキモはクライアントのユーザーをSQL serverの
ローカルユーザーに同じ名前とPWで登録してかつSQL serverにも
登録して権限を与える。なんか矛盾してるような気もするが
これでクライアントからも接続できた。
accdeならコネクターの名前は開発元と同じ名前にする。これもキモ。

SQL server native clientは宿題とする

しかしOSは64bitだけどACCESSが32bitなのでコネクターも32ビット
なんですよね。
VBAでクエリ文書いているときも今ACCESS側のクエリなのか
SQL側のクエリなのかも時々混乱します。
まるで北斗琉拳の魔闘気の中にいるように自分の立ち位置がわからなくなります。
暗琉天破、受けたことはないですが・・。

Round 五捨五超入 

四捨五入する関数ROUNDはここに名前の出てくるソフトには
だいたいその名前で組み込まれているのですがACCESSのRound関数
だけは四捨五入ではなくて銀行丸め、ISO丸めと言うものでおなじ
端数処理ではあるが全く別物

探したけれどなんとACCESSには四捨五入の関数はなさそう。
formatでできる!?

ところで五捨五超入という薬局で使う端数処理があるのですが・・
5までは切り捨て、5を超えたら繰り上げる
銀行丸めとは似てはいるけどちょっと違う。銀行丸めほど複雑ではない。
薬代を算定するのに必要です。薬代というのを調べてみると
小数点第2位までらしい。
薬価は薬代/10だから小数点第3位までになる?
ならばAという薬価の小数点以下を五捨五超入したい場合は
A-0.0001を四捨五入すれば同じになるかな。

sqlserverのROUNDでround((A-0.001),0)

Aの型がfloatというのがちょっと引っかかる
落とし穴がありそう

ほか今日はACCESSフォームで式で型が一致しませんのアラート
ソースクエリのリンクしているColumnの型を間違えて保存してしまっていた。

MSSQLとMySQL違い#日付

違いは色々あると思うけど備忘録
MySQLで日付だけ取得したい場合 current_date
MSSQLでは convert(date,getdate())

追加
システム時刻取得は
MSSQLはgetdate()
mysqlはNow
ちなみにACCESSもNow

ODBC接続MSSQLテーブルに更新クエリは

ODBC接続MySQLテーブルに更新クエリが競合エラー と書いたけれど
バックエンドDBがMSSQLだったら? SQL Server 2016 をバックエンドにNorthWind.accdbで実験

ODBC設定を開くとMSSQL関係は2つある?よくわからないがSQL Serverを選択
MYSQLのようなReturn matched rows instead of affected rowsのODBC設定が見つからない
MSSQLにはshow processlist はないのでコマンドプロンプトでnetstat | find “ポート” で確認

競合エラーは起きないものの”*** where column=(?)” cmd.execute,param 更新クエリ発行では接続が増え続ける。
“*** where column=” & param では増えない。
バックエンドはMSSQLに決定して
ODBCとcurrentproject.connectionはやめて接続文字列のdirectなんちゃら接続で進めていくこととする

今日は無題

カラム数が100を超えるようなテーブルを扱う際、あるカラム名が存在するのかどうか確認するのが面倒なので

Public Function isnullColumn(paramColumnName As String, paramTableName As String) As Boolean

なるものを・・バックエンドはMSSQL

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.ConnectionString = “接続文字列”
cn.Open
rs.Open “select column_name from DBの名前.information_schema.columns where table_name='” & paramTableName & “‘”, cn

rs.MoveFirst
  Do Until rs.EOF
If rs!column_name = paramColumnName Then
isnullColumn = False
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
exit function
End If
rs.MoveNext
Loop

isnullColumn = True

rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Function

最初丁寧にexitでループを抜けていたがそれはここではX
罠にかけたら即終わるべし・・かな?