SQLserver 連番をリセットする方法

連番設定してあると、データを消した時に、その連番が保持されていて次からの発番になる。
1からスタートしたい場合は、下記のクエリを実行すればリセットされる。

IDENTITYプロパティを設定した列のID値を初期化(リセット)する方法

— ID値を0に設定
DBCC CHECKIDENT (‘tablename’, RESEED, 0);

— ID値を100に設定
DBCC CHECKIDENT (‘tablename’, RESEED, 100);

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