A while ago I wrote an article on code generation using Sql. The script I gave in that article can generate BCP (in and out) scripts automatically.

I helped out a friend recently, and thought I would share the WMI/VBScript I cooked up with you. This script assumes that all files within a folder need to go to one table, and the layout of the comma delimited files match the structure of the table. It also uses trusted authentication. It can be easily customized for more advanced BCP tasks. Enjoy!

strComputer = "."
'Directory name. Don't put the back slash here
strDirectory = "C:\MyDirectoryWhereCsvFilesAre"
'Instance Name
strInstance = "MySqlServerInstance"
'Table Name
strTableName = "MyDb.dbo.MyTable"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set oShell = CreateObject("WScript.Shell")

Set colFiles = objWMIService.ExecQuery _
    ("ASSOCIATORS OF {Win32_Directory.Name='" & strDirectory & "'} Where " _
        & "ResultClass = CIM_DataFile")

For Each objFile In colFiles
    strCommand = "bcp " & strTableName & " in " & chr(34) & strDirectory & "\" & objFile.FileName & "." & objFile.Extension & chr(34) & " -c -T -S " & strInstance & " -t " & chr(34) & "," & chr(34)
    'msgbox strCommand
    oShell.Run strCommand, 0, True
Next