# マクロ

Sub delete()

Dim sql As String
Dim row As Long
Dim coloumn As Long

row = 3

Do Until Cells(row, 3) = ""
sql = "DELETE FROM " + Cells(1, 1) + " WHERE "

coloumn = 3
Do Until Cells(2, coloumn) = ""
sql = sql + Cells(2, coloumn) + " = '" + Cells(row, coloumn).Value + "' AND "

```   'C1のセルの値分のカラム数まででexit
If coloumn = 2 + Cells(1, 3).Value Then Exit Do

coloumn = coloumn + 1
```

Loop

'「 AND」の削除
sql = Left(sql, Len(sql) - 4)
sql = sql + ";"

'delete文の出力
Cells(row, 1) = sql
row = row + 1
Loop

End Sub

Sub insert()

Dim sql_coloumn As String
Dim sql As String
Dim row As Long
Dim coloumn As Long
Dim coloumnCount As Long

'insert文出力
row = 3
coloumnCount = 3
coloumn = 3
sql_coloumn = "INSERT INTO " + Cells(1, 1) + " ("

'カラム名を設定
Do Until Cells(2, coloumn) = ""
sql_coloumn = sql_coloumn + Cells(2, coloumn) + ","
coloumn = coloumn + 1
coloumnCount = coloumnCount + 1
Loop

sql_coloumn = Left(sql_coloumn, Len(sql_coloumn) - 1) '「,」の削除

sql_coloumn = sql_coloumn + ") VALUES ("

Do Until Cells(row, 3) = ""
'値の設定
coloumn = 3
sql = sql_coloumn
Do Until coloumn = coloumnCount
'DBNULLの場合は"'(シングルコート)"はつけない
If Cells(row, coloumn).Value = "NULL" Or Cells(row, coloumn).Value = "(NULL)" Then
sql = sql + Cells(row, coloumn).Value + ","
Else
sql = sql + "'" + Cells(row, coloumn).Value + "'" + ","
End If
coloumn = coloumn + 1
Loop

sql = Left(sql, Len(sql) - 1) '「,」の削除
sql = sql + ");"

'insert文の出力
Cells(row, 2) = sql
row = row + 1
Loop

'シート「設定」B2の値が「有」だった場合ファイル出力
If Sheets("設定").Cells(2, 2).Value = "有" Then
Dim datFile As String
Dim outputCount As Long
Dim creatDataCount As Long
Dim i As Long
Dim outputName As Long
Dim deleteCount As Long
Dim insertCount As Long

creatDataCount = 0

'生成したdelete文の数をカウント
i = 3
Do While Cells(i, 1).Value <> ""
creatDataCount = creatDataCount + 1
i = i + 1
Loop

'1ファイルに出力する行数（delete文 + insert文なので、/2）
outputCount = Sheets("設定").Cells(4, 2).Value / 2

outputName = 1
deleteCount = 3
insertCount = 3
Do While creatDataCount <> 0
'同一階層に、A1のテーブル名 + "" + ナンバリング（例：M_WM_ZONE_1.txt）
datFile = ActiveWorkbook.Path & "\" & Cells(1, 1).Value & "
" & outputName & ".txt"

```   'ファイル生成
Open datFile For Output As #outputName

'delete文
Do While Cells(deleteCount, 1).Value <> ""
Print #outputName, Cells(deleteCount, 1).Value

deleteCount = deleteCount + 1

If deleteCount = outputCount + 3 Then
Exit Do
End If
Loop

'insert文
Do While Cells(insertCount, 2).Value <> ""
Print #outputName, Cells(insertCount, 2).Value

insertCount = insertCount + 1

If insertCount = outputCount + 3 Then
Exit Do
End If
Loop

'ファイルclose
Close #outputName

'生成したDDL数 < ファイル出力行数 の場合はループ終了
If creatDataCount < outputCount Then
creatDataCount = 0
Else
creatDataCount = creatDataCount - outputCount
outputCount = outputCount + Sheets("設定").Cells(4, 2).Value / 2
End If

outputName = outputName + 1
```

Loop
End If

End Sub