SQLServer テーブル定義の取得

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

テーブル定義 取得用 SQL

lesson004.sql

select

    c.column_name

,   c.data_type

from

    information_schema.columns      c

where

        (   c.table_name    =   '%table_name%'      )

order by

    c.ordinal_position

接続文字列

lesson002.con

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

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

lesson004.vbs

Option Explicit

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 tableInfos
Set tableInfos = CreateObject("Scripting.Dictionary")
If Not rsSqlServer.EOF Then
    Do Until rsSqlServer.EOF
        tableInfos.Add rsSqlServer.Fields("column_name").Value & "", rsSqlServer.Fields("data_type").Value & ""
        rsSqlServer.MoveNext
    Loop
End If
rsSqlServer.Close

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

'Access 読み込み
Dim rsAccess' As ADODB.Recordset
Set rsAccess = CreateObject("ADODB.Recordset")
With rsAccess
    .Source           = "SELECT * FROM " + WScript.Arguments(4)
    .Activeconnection =  cnAccess
    .CursorType       =  0' adOpenForwardOnly
    .LockType         =  1' adLockReadOnly
    .Open
End With

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

            Dim columnName: columnName = rsAccess.Fields(i).Name

            sSql = sSql & "@" & Left(columnName & Space(32), 32) & " = "

            Select Case tableInfos.Item(columnName)
                Case "nvarchar", "nchar", "varchar", "char"
                    sSql = sSql & "'" & rsAccess.Fields(i).Value & "'" & vbNewLine

                Case "datetime", "smalldatetime"
                    If rsAccess.Fields(i).Value & "" = "" Then
                        sSql = sSql & "NULL" & vbNewLine
                    Else
                        sSql = sSql & "'" & rsAccess.Fields(i).Value & "'" & vbNewLine
                    End If

                Case Else
                    If rsAccess.Fields(i).Value & "" = "" Then
                        sSql = sSql & "NULL" & vbNewLine
                    Else
                        sSql = sSql & rsAccess.Fields(i).Value & vbNewLine
                    End If
            End Select
                
        End If
    Next
    cnSqlServer.Execute sSql

    rsAccess.MoveNext
Loop

rsAccess.Close
Set rsAccess = Nothing

'Access 切断
cnAccess.Close
Set cnAccess = Nothing

'SQLServer 切断
cnSqlServer.Close
Set cnSqlServer = Nothing

Set tableInfos = Nothing

実行形式

C:\>cscript c:\study\vbscript\chapter007\lesson004.vbs  c:\study\vbscript\chapte

r007\lesson002.con c:\study\vbscript\chapter007\lesson004.sql DimAccount c:\stud

y\vbscript\chapter007\lesson002.mdb IMPORT_DimAccount //nologo

実行イメージ

MERGE_DimAccount

    @AccountKey                       = 1

,   @ParentAccountKey                 = NULL

,   @AccountCodeAlternateKey          = 1

,   @ParentAccountCodeAlternateKey    = NULL

,   @AccountDescription               = 'Balance Sheet'

,   @AccountType                      = ''

,   @Operator                         = '~'

,   @CustomMembers                    = ''

,   @ValueType                        = 'Currency'

,   @CustomMemberOptions              = ''
MERGE_DimAccount

    @AccountKey                       = 2

,   @ParentAccountKey                 = 1

,   @AccountCodeAlternateKey          = 10

,   @ParentAccountCodeAlternateKey    = 1

,   @AccountDescription               = 'Assets'

,   @AccountType                      = 'Assets'

,   @Operator                         = '+'

,   @CustomMembers                    = ''

,   @ValueType                        = 'Currency'

,   @CustomMemberOptions              = ''
MERGE_DimAccount

    @AccountKey                       = 3

,   @ParentAccountKey                 = 2

,   @AccountCodeAlternateKey          = 110

,   @ParentAccountCodeAlternateKey    = 10

,   @AccountDescription               = 'Current Assets'

,   @AccountType                      = 'Assets'

,   @Operator                         = '+'

,   @CustomMembers                    = ''

,   @ValueType                        = 'Currency'

,   @CustomMemberOptions              = ''
...