SQLserver 2つのテーブルの差分を追加

テーブルA と テーブルBがあったとして、両テーブルを比較して、テーブルAにあってテーブルBに無い時に、テーブルBにレコードを追加していくクエリ

※比較する列は、G列とF列とする。

INSERT into dbo.テーブルB
SELECT ‘20161129’,生徒CD,スクールCD,ヨヤクルCD,0
FROM dbo.テーブルA
WHERE NOT EXISTS(
select ‘x’
from dbo.テーブルB
where G列 = dbo.テーブルA.G列
AND F列 = dbo.テーブルA.F列

android LINEゲーム 認証できない

LINEポコポコや、LINEディズニーツムツム、SNOW(スノー)などの、「LINEログイン(LINE認証)」を行うアプリにて、LINEアプリの「認証」画面の表示が崩れてしまい、ボタンも押すことができず、LINE認証ができない問題が発生しています

現在、LINE関連アプリやLINEゲームアプリに「LINEログイン」する際に開かれるLINEアプリの「認証」画面が正しく表示されず、LINE認証できない問題が発生しています(デザインが崩れてボタンが押せない)

この問題の原因は、Androidの「WebView」というアプリ内ブラウザ用コンポーネントの不具合が原因です。

この問題を修正するためには、「WebView」をアップデートしてください。

記 金子 健

 

SQLserver テーブルからテーブルへコピー

分かっている人なら簡単な事なのだろうが、初心者にはそれさえも一苦労です。

テーブルからテーブルにデータをコピーする方法を記載しておきます。

全くの同じテーブル構成に全部をコピーする場合は、

既に同じ定義のテーブルがあるのであれば
INSERT into tblB SELECT * FROM tblA;

でもtblBの定義がしていないのであれば、
CREATE table tblB AS SELECT * FROM tblA;
※テーブル作成ですかね。

 

INSERT into dbo.ポイントマスタ SELECT yoyacleID,’2016/01/01′,’2050/12/31′,0 FROM dbo.TM020_スクールマスタ

ポイントマスタ テーブルにスクールマスタから、yoyacleID,日付、日付,0 をコピーした。

yoyacleID以外は、直入れですね。

 

SQLserver テーブル作成時のデータ型について

下記のサイトの内容を転記しています。詳細は下記を。

【初級編⑤】SQL Server 2008 のデータ型とサイズについて

 

種別 データ型 説明 サイズ(バイト)
整数型 tinyint 0 ~ 255 の範囲の数値 1
smallint おおよそ -3万 ~ 3万の範囲の数値 2
int おおよそ -20億 ~ 20億の範囲の数値 4
bigint おおよそ -900京 ~ 900京の範囲の数値 8
小数(真数型) decimal(p, s) p:全体桁数 s:小数点以下桁数
固定長の有効桁数と小数点以下桁数を持つため誤差は発生しない(10進数)
5 ~ 17
numeric(p, s)
小数(概数型) float(n) n:仮数のビット数 浮動小数点数 4 ~ 8
real 浮動小数点数 4
文字列 char(n) 固定長文字列(8000バイトまで) n
varchar(n) 可変長文字列(8000バイトまで) 実際のバイト数 + 2
varchar(max)
text
可変長文字列(2GBまで)
Unicode文字列 nchar(n) Unicode固定長文字列(8000バイトまで) 2n
nvarchar(n) Unicode可変長文字列(8000バイトまで) (実際のバイト数) * 2 + 2
nvarchar(max)
ntext
Unicode可変長文字列(2GBまで)
日付 smalldatetime 精度:1分 4
datetime 精度:3.33ミリ秒 8
date 精度:1日 3
time 1日の時刻のみを格納し、精度は100ナノ秒 3~ 5
datetime2 精度:100ナノ秒 6 ~8
datetimeoffset 精度:100ナノ秒 タイムゾーン付き 8 ~10
金額・通貨 smallmoney 金額や通貨を表す(範囲狭) 4
money 金額や通貨を表す(範囲広) 8
バイナリ binary(n) 固定長バイナリデータ(8000バイトまで) n
varbinary(n) 可変長バイナリデータ(8000バイトまで) 実際のバイト数 + 2
varvinary(max)
image
可変長バイナリデータ(2GBまで)
その他 bit 1, 0, NULL のどれか 1
table SELECT文実行結果のテーブルを格納可能 実際のデータ量
XML XMLデータを格納可能
uniqueidentifier 16バイトのGUID(グローバルユニークID) 16
timestamp
(rowversion)
テーブルのデータが更新される度に加算されるカウンタ(行バージョンを格納) 8
sql_variant 複数のデータ型を格納可能 実際のバイト数

 

 

データ型 使用するサイズ(バイト数) 扱える数値の範囲
tinyint 1 0 ~ 255
smallint 2 -32,768 ~ 32,767
(おおよそ -3万から3万)
int 4 -2,147,483,648 ~ 2,147,483,647
(おおよそ -20億から20億)
bigint 8 -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
(おおよそ -900京から900京)

バッチファイルの日付

全日の日付を求めるバッチファイル

REM 対象日付
SET YYYYMMDD=%DATE:~-10%
SET YY=%YYYYMMDD:~0,4%
SET MM=%YYYYMMDD:~5,2%
SET DD=%YYYYMMDD:~8,2%

#########当日日付#########
ECHO 対象日付 %YY%年 %MM%月 %DD%日
######################
※だた表示させているだけなので、削除してOK

REM 前日を求める
SET /A DD=1%DD%-1
SET DD=%DD:~-2%

REM (グレゴリオ暦)閏年か
SET /A LEAP=%YY% %% 400
IF NOT %LEAP%==0 SET /A LEAP=%YY% %% 100 – 1
IF NOT %LEAP%==-1 SET /A LEAP=%YY% %% 4

REM 月またぎの対応
IF “X%DD%”==”X00” (
IF “X%MM%”==”X01” (SET MM=12& SET DD=31& SET /A YY=%YY%-1)
IF “X%MM%”==”X02” (SET MM=01& SET DD=31)
IF “X%MM%”==”X03” (SET MM=02& SET DD=28& IF %LEAP%==0 SET DD=29)
IF “X%MM%”==”X04” (SET MM=03& SET DD=31)
IF “X%MM%”==”X05” (SET MM=04& SET DD=30)
IF “X%MM%”==”X06” (SET MM=05& SET DD=31)
IF “X%MM%”==”X07” (SET MM=06& SET DD=30)
IF “X%MM%”==”X08” (SET MM=07& SET DD=31)
IF “X%MM%”==”X09” (SET MM=08& SET DD=31)
IF “X%MM%”==”X10” (SET MM=09& SET DD=30)
IF “X%MM%”==”X11” (SET MM=10& SET DD=31)
IF “X%MM%”==”X12” (SET MM=11& SET DD=30)
)

##########前日日付###########
REM 計算結果
ECHO 計算結果 %YY%年 %MM%月 %DD%日
#########################
※ただ表示させているだけなので削除してOK

set filename=%YY%%MM%%DD%.csv などでファイル名を前日の日付でセットできる。

バッチ処理で、日付ファイルを処理する。

先日、”名前”で囲まれているCSVファイルから、””がSQLserverにインポートするときに邪魔なので、削除するバッチファイルを見つけた。

では、毎日読み込まれたCSVファイルを処理するにはという事で備忘録にする。

下記のバッチファイルを作成すれば完了です。callでコンバートするバッチファイルを日付CSVに読み込ませて処理させている。

@echo off

echo %date%
echo %time%

set yyyy=%date:~0,4%
set mm=%date:~5,2%
set dd=%date:~8,2%

set filename=%yyyy%%mm%%dd%
call c:\point\convert.bat c:\point\%filename%.csv

SQLserver MSSMSの上位200件の編集の変更

SQLserverのデータ編集で、MSSMSの上位200件の編集ってのがあったが、全部表示させてほしいとか、もっとっていう時がある。その数値は変更できるようだ。

メニューから

「ツール」→「オプション」→「SQL Serverオブジェクトエクスプローラー」→「コマンド」と進む。
そうすると右のエリアに「上位N件の件数」についての設定があるので、その値を0にしてやると、先ほどの

上位1000件の選択
上位200件の編集

全ての行の選択
全ての行の編集
に変わり、これで、全行を閲覧/編集することが可能。

ただし、SQL Server Management Studioは、データを一気に取得して全部表示するので、大量にデータが存在するテーブルの場合はフリーズする可能性があるので注意が必要。

SQLserver 列の違うcsvファイルをインポート 連番

SQLserverの性質が分からず使っていますが、自動でCSVファイルをSQLSERVERのテーブルにインポートしたい。
ただし、SQLserverでは、後々インポートした後処理をしたら削除したいので、主キーが無いと削除できないので、csvファイルの列よりも1列 NO という列を追加して主キーを付けてテーブルを準備した。

ちなみにテーブル構造から、その主キーのプロパティを開き、IDENTITY列っていうところに、NOをドロップダウンから選択して設定しておくとデータをインポートしたときに、自動で連番がふられるという仕組み。

インポートテーブルとCSVデータが同じ列数ならば、クエリからBULK INSERTにて、インポートが可能だ。

しかし、列が違うと面倒な手続きが必要になる。

まず、実機から、フォーマットファイルというのを作成する。

下記の例では、testdbがデータベース名 dbo.tableがテーブル名 JOYSQL\SQLEXPRESS がサーバーとインスタンス名となる。あらかじめ、インポート先のテーブルは作成しておき、フォーマットファイルを作成する手順。

(1)bcp testdb.dbo.table format nul -T -c -t “,” -f C:\data\table.fmt -S JOYSQL\SQLEXPRESS

※サーバーとインスタンスの設定をしないと、接続できないよってエラーが発生する。

上記のコマンドをバッチファイルにして実行してもいいし、直接DOSプロンプトに打ち込んでもOK
その結果

(2)dataディレクトリにtable.fmtというフォーマットファイルができる。
※フォーマットファイルは、xml形式の物もできるようだ。

13.0
5
1 SQLCHAR 0 12 “,” 1 NO “”
2 SQLCHAR 0 24 “,” 2 日付 “”
3 SQLCHAR 0 7 “,” 3 スクールCD “”
4 SQLCHAR 0 100 “,” 4 ヨヤクルCD Japanese_CI_AS
5 SQLCHAR 0 100 “\r\n” 5 氏名 Japanese_CI_AS

今回は、最初の列を飛ばしたいので、1の行の列を下記のようにスキップさせる様に書き換えて保存。
※1行目はNULLを書いて飛ばしているようだ。でも、連番の設定をしているので自動で振られる。

13.0
5
1 SQLCHAR 0 0 “” 0 NO “”
2 SQLCHAR 0 24 “,” 2 日付 “”
3 SQLCHAR 0 7 “,” 3 スクールCD “”
4 SQLCHAR 0 100 “,” 4 ヨヤクルCD Japanese_CI_AS
5 SQLCHAR 0 100 “\r\n” 5 氏名 Japanese_CI_AS

次に実際にインポートコマンド

(3)bcp testdb.dbo.table in C:\point\20161110.csv -T -f C:\data/table.fmt -S JOY2016SQL\SQLEXPRESS

これで、20161110.csvが、dbo.tableに無事にインポートされた。

(1)と(3)をバッチファイルにして、定期実行させれば自動でインポートができるようになる。

ただ、毎日の日付ファイルをインポートしたいので、ちょっとした小細工がひつようになる。
以前、JOY備忘録でも、日付の自動バッチファイルを作成したことがある。
それを参考に毎日、インポートできるようにしていくのが次の課題だ。

バッチファイルで、” を削除する

CSVファイルからSQLserverにインポートするときに、ダブルコーテーションが邪魔をする場合がある。

その時、下記のバッチファイルを実行させれば、すべてのダブルコーテーションを削除して整えてくれる。

@echo off
if “%1″==”” goto end
set fname=%1
copy %fname% org >nul
type nul >%fname%
for /f %%a in ( org ) do (set line=%%a&& call :sub)

del org
goto end

:sub
set l=%line:”=%
>>%fname% echo %l%
goto :EOF

:end

バッチファイル後ろに変換したいファイルを入れてDOSプロンプトから実行させると変換される。