SQLServer テーブル定義の取得

SQLServer VBScript ADO Excel

Excel のデータを読んで SQLSerevr 上のテーブルを更新する

テーブル定義 取得用 SQL

lesson011.sql



select

    c.column_name

,   c.data_type

from

    information_schema.columns      c

where

        (   c.table_name    =   '%table_name%'      )

order by

    c.ordinal_position

接続文字列

lesson004.con



DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;

テーブル更新用スクリプト

lesson011.vbs

Private fs
Set fs = CreateObject("Scripting.FileSystemObject")

'SQLServer 接続文字列 読み込み
Private tsCon
Set tsCon = fs.OpenTextFile(WScript.Arguments(0))

Private sCon: sCon = ""
If Not tsCon.AtEndOfStream Then
    sCon = tsCon.ReadLine
End If
tsCon.Close
Set tsCon = Nothing

'SQLServer 接続
Private cnSqlServer
Set cnSqlServer = CreateObject("ADODB.Connection")
With cnSqlServer
    .Provider         = "SQLOLEDB"
    .ConnectionString = sCon
    .Open
End With

'テーブル定義情報 取得用 SQL 読み込み
Private tsSql
Set tsSql = fs.OpenTextFile(WScript.Arguments(1))

Private sSql: sSql = ""
Do Until tsSql.AtEndOfStream
    sSql = sSql & tsSql.ReadLine & vbNewLine
Loop
tsSql.Close
Set tsSql = Nothing
Set fs    = Nothing

'テーブル名を指定
Dim tableName: tableName = WScript.Arguments(2)
sSql = Replace(sSql, "%table_name%", tableName)

Dim rsSqlServer
Set rsSqlServer = CreateObject("ADODB.Recordset")
With rsSqlServer
    .ActiveConnection = cnSqlServer
    .CursorType       = 0 'adOpenForwardOnly
    .LockType         = 1 'adLockReadOnly
    .Source           = sSql
    .Open
End With

Dim fieldCount: fieldCount = -1
Dim fieldNames()
Dim fieldTypes()
Dim constraints()

If Not rsSqlServer.EOF Then
    Do Until rsSqlServer.EOF
        fieldCount = fieldCount + 1

        ReDim Preserve fieldNames(fieldCount)
        ReDim Preserve fieldTypes(fieldCount)

        fieldNames(fieldCount)  = rsSqlServer.Fields("column_name").Value     & ""
        fieldTypes(fieldCount)  = rsSqlServer.Fields("data_type").Value       & ""

        rsSqlServer.MoveNext
    Loop
End If
rsSqlServer.Close

'Excel 接続
Dim cnExcel' as ADODB.Connection
Set cnExcel = CreateObject("ADODB.Connection")
With cnExcel
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & WScript.Arguments(3) & ";" & "Extended Properties=Excel 8.0;"
    .CursorLocation = 3' adUseClient
    .Open
End With

'Excel 読み込み
Dim rsExcel' As ADODB.Recordset
Set rsExcel = CreateObject("ADODB.Recordset")
With rsExcel
    .Source           = "SELECT * FROM [" + WScript.Arguments(4) + "$]"
    .Activeconnection =  cnExcel
    .CursorType       =  0' adOpenForwardOnly
    .LockType         =  1' adLockReadOnly
    .Open
End With

Do Until rsExcel.EOF
    sSql = "MERGE_" & tableName & vbNewLine
    For i = 0 To rsExcel.Fields.Count - 1
        If i <= fieldCount Then
            If i = 0 Then
                sSql = sSql & "    "
            Else
                sSql = sSql & ",   "
            End If

            Select Case fieldTypes(i)
                Case "nvarchar", "nchar", "varchar", "char"
                    sSql = sSql & "'" & rsExcel.Fields(i).Value & "'" & vbNewLine
                Case "datetime", "smalldatetime"
                    If rsExcel.Fields(i).Value & "" = "" Then
                        sSql = sSql & "NULL" & vbNewLine
                    Else
                        sSql = sSql & "'" & rsExcel.Fields(i).Value & "'" & vbNewLine
                    End If
                Case Else
                    If rsExcel.Fields(i).Value & "" = "" Then
                        sSql = sSql & "NULL" & vbNewLine
                    Else
                        sSql = sSql & rsExcel.Fields(i).Value & vbNewLine
                    End If
            End Select
                
        End If
    Next
    cnSqlServer.Execute sSql

    rsExcel.MoveNext
Loop

rsExcel.Close
Set rsExcel = Nothing

'Excel 切断
cnExcel.Close
Set cnExcel = Nothing

'SQLServer 切断
cnSqlServer.Close
Set cnSqlServer = Nothing
実行形式



C:\>cscript c:\study\vbscript\chapter006\lesson011.vbs  c:\study\vbscript\chapte

r006\lesson004.con c:\study\vbscript\chapter006\lesson011.sql DimAccount c:\stud

y\vbscript\chapter006\lesson007.xls Sheet1 //nologo

実行イメージ



MERGE_DimAccount

    1

,   NULL

,   1

,   NULL

,   'Balance Sheet'

,   ''

,   '~'

,   ''

,   'Currency'

,   ''
MERGE_DimAccount

    2

,   1

,   10

,   1

,   'Assets'

,   'Assets'

,   '+'

,   ''

,   'Currency'

,   ''
MERGE_DimAccount

    3

,   2

,   110

,   10

,   'Current Assets'

,   'Assets'

,   '+'

,   ''

,   'Currency'

,   ''
...