ひできちの楽天ブログ

 
Sub CreateInsertSQL()

Dim ws As Worksheet
Dim tableName As String
Dim outputPath As String
Dim truncateFlag As String
Dim lastRow As Long
Dim lastCol As Long
Dim colNames As String
Dim sql As String
Dim i As Long, j As Long
Dim recordValues As String
Dim cellValue As String
Dim stm As Object  'ADODB.Stream

Set ws = ActiveSheet

'--- 基本情報 ---
tableName = Trim(ws.Range("B1").Value)
outputPath = Trim(ws.Range("B2").Value)
truncateFlag = LCase(Trim(ws.Range("B4").Value))

'--- カラム数・行数 ---
lastCol = ws.Cells(8, ws.Columns.Count).End(xlToLeft).Column
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'--- カラム名 ---
colNames = ""
For j = 1 To lastCol
colNames = colNames & ws.Cells(8, j).Value & ", "
Next j
colNames = Left(colNames, Len(colNames) - 2)

'--- SQL 開始 ---
sql = ""

If truncateFlag = "truncate" Then
sql = sql & "TRUNCATE TABLE " & tableName & ";" & vbCrLf & vbCrLf
End If

sql = sql & "INSERT INTO " & tableName & " (" & colNames & ") VALUES" & vbCrLf

'--- レコード生成 ---
For i = 9 To lastRow

recordValues = "("

For j = 1 To lastCol

cellValue = Trim(ws.Cells(i, j).Value)

If cellValue = "" Then
recordValues = recordValues & "NULL, "
Else
recordValues = recordValues & "'" & Replace(cellValue, "'", "''") & "', "
End If

Next j

recordValues = Left(recordValues, Len(recordValues) - 2)
recordValues = recordValues & "),"

sql = sql & recordValues & vbCrLf
Next i

sql = Left(sql, Len(sql) - 3) & ";" & vbCrLf

'===========================================================
' UTF-8（BOMなし）でファイル出力（ADODB.Stream）
'===========================================================
Set stm = CreateObject("ADODB.Stream")
stm.Type = 2                'Text
stm.Charset = "UTF-8"       'UTF-8 指定
stm.Open
stm.WriteText sql
stm.Position = 0
stm.SaveToFile outputPath, 2 '2 = adSaveCreateOverWrite
stm.Close

MsgBox "INSERT SQLファイル作成が完了しました。", vbInformation

End Sub
 

 
TRUNCATE TABLE SNOFLAKE.SHCHEMA.TABLEA;

INSERT INTO SNOFLAKE.SHCHEMA.TABLEA (col1, col2, col3, col4, col5) VALUES
('1', 'tset2', '2026/01/22', NULL, '1234567890123456789012345678901234567890'),
('2', 'テスト', '2026/01/20', NULL, '123456789');