SQLserver インポートエラー 列リスト IDENTITY_INSERT

SQLserverでデータをインポートしようとすると、「列リストを使用し、IDENTITY_INSERTをONにしてから実行」のエラーがでる。
どうも連番の設定がしているテーブルにデータを入れるときにコツがいるようだ。

結局下記のようなクエリにしたらOKになった。

SET IDENTITY_INSERT mst_openparts ON
INSERT INTO mst_openparts(GCD, PNO, 製品名コード, 順番, HNO, 品名コード, 品名, 型式, 部品数)
SELECT GCD, PNO, 製品名コード, 順番, HNO, 品名コード, 品名, 型式, 部品数
FROM [マスタ>部品展開]
SET IDENTITY_INSERT mst_openparts OFF

つまりは、細かく列を設定しないとエラーになるようだ。

SQLserver 既存のテーブルに連番を振りたい

簡単そうでできない事だったので備忘録にする。

主キーが無いと更新ができないのだが、テーブル上、主キーにしたいキーが重複しているので新たな主キーがないとテーブルの役目を果たせなくなってしまった。

そこで、Accessで言うオートナンバーを探した。なんだか難しかったので、Excelでそのレコードの番号を作成して、テーブルに貼り付ければいいやって簡単に考えていたが、貼り付けができない。
※貼り付けボタンがONにならない。
結局諦めて別の方法で回避できたので、その方法を書く。

(1)SQLserverの連番を振りたいテーブル(以下テーブルB)の構造を開いて、新しいテーブル(以下テーブルA)を作成する。
全く同じ構造で中身がないテーブルを作成する。

(2)主キーにするための、列(以下CD)をテーブルAに追加する。

(3)テーブルAの主キーにする列(CD)のプロパティを開いて、「IDENTITYの指定」を(IDである)を”はい”にする。そうすると、(IDの増分)が勝手に1になり、1づつ連番が振られる設定になるようだ。

(4)後は、クエリを動かして、インサートすれば、自動で連番が振られている。

INSERT INTO テーブルA
(PNO, 製品名コード, 順番, HNO, 品名コード, 品名, 型式, 部品数)
SELECT PNO, 製品名コード, 順番, HNO, 品名コード, 品名, 型式, 部品数
FROM テーブルB

テーブルBのデータをテーブルAに丸ごとインサートしている。
テーブルAは、CD,PNO, 製品名コード, 順番, HNO, 品名コード, 品名, 型式, 部品数
なのだが、CD列内に自動で連番が振られることになる。

ちなみに、列をいちいち手入力でやるのは面倒なので、クエリデザイナーを上手につかって選択入力してから、手を加えて整形した。

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 列の違う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備忘録でも、日付の自動バッチファイルを作成したことがある。
それを参考に毎日、インポートできるようにしていくのが次の課題だ。

Access 日付込みデータインポート

あるデータをインポートすると、インポートエラーが出て3個くらい勝手にテーブルを作成する。このエラーは、データ型の相違や、主キー、ファイル形式の違い、先頭行の問題などがあるようだ。

今回は、インポートした後に、勝手に作成されたインポートエラーテーブルを自動で削除するもの。

ボタンをクリックしたら、同時に作業を実行させる。

Private Sub 個配指示_Click()
DoCmd.TransferText _
TransferType:=acImportDelim, _
specificationname:=”配達完了実績ファイル インポート定義”, _
TableName:=”インポートHaitasu”, _
FileName:=”D:\shop\finished\haitatsu_” & Format(Now(), “yyyymmdd”) & “00.txt”
Table削除
End Sub

※haitatsu2016012200.txtというファイルを「インポートHaitatsu」テーブルにインポートする場合を想定している。

インポートエラーテーブル削除対応の為、下記のVBAを設置しておく。

Sub Table削除()
Dim DB As DAO.Database
Dim TableLoop As TableDef
Dim strTname As String
Set DB = CurrentDb

For Each TableLoop In DB.TableDefs

strTname = TableLoop.Name

If Right(strTname, 3) = “エラー” Then
DoCmd.DeleteObject acTable, strTname
Exit For
End If

Next TableLoop

End Sub