INSERT statements for your SQL database

The ability to script data from an existing table, ( generate SQL INSERT statements ) is required by all DBA's at one time or another. This piece of sample code will generate these INSERT statements.
---

option explicit

dim connectString
dim tableName
dim fileName

' change the following three lines to point to the server, database, and table required. 

connectString = "Provider=SQLOLEDB;User Id=sa;Password=password;
          Initial Catalog=MyCatalogName;Data Source=MyDataSourceName;"
tableName = "MytableName"
fileName = "MyInsert.sql"

dim oFS
dim oFile

set oFS = CreateObject("Scripting.FileSystemObject")
set oFile = oFS.CreateTextFile(fileName, true) ' will overwrite an existing file

dim oConnection
dim oCommand
dim oRS

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open connectString
set oCommand = CreateObject("ADODB.Command")
set oCommand.ActiveConnection = oConnection
oCommand.CommandText = "SELECT * FROM " + tableName

set oRS = oCommand.Execute
oRS.MoveFirst

PutFileHeader oFile

while not oRS.EOF
   PutStatement oRS, oFile
   oRS.MoveNext
wend

PutFileFooter oFile

Wscript.Echo "Complete"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub PutFileHeader(oFile)

  oFile.WriteLine("BEGIN TRANSACTION")
  oFile.WriteLine("SET IDENTITY_INSERT " + tableName + " ON")

End Sub

Sub PutFileFooter(oFile)

  oFile.WriteLine("GO")
  oFile.WriteLine("SET IDENTITY_INSERT " + tableName + " OFF")
  oFile.WriteLine("COMMIT")

End Sub

Sub PutStatement(oRS, oFile)

  dim i
  dim line
 
  line = "INSERT INTO " + tableName + " ("

  line = line + GetFieldNames(oRS.Fields)

  line = line + ")  VALUES( "

  for i = 0 to oRS.Fields.Count - 1
    line = line + GetFieldValue(oRS.Fields.Item(i))
     if i < oRS.Fields.Count -1 then
       line = line + ","
     end if
  next

  line = line + ")"

  oFile.WriteLine(line)

End Sub

Function GetFieldNames(oFields)
 
  dim i
  redim names(oFields.Count)
  
  for i = 0 to oFields.Count - 1
    names(i) = oFields.Item(i).Name
  next

  dim result
  result = Join(names, ",")
  
  GetFieldNames = Left(result, Len(result)-1)

End Function

Function GetFieldValue(oField)
   ' ADO's DataTypeEnum values (see ADO 2.7 API reference)
   const adChar = 129
   const adVarChar = 200
   const adBoolean = 11
   const adDate = 135

   dim value
   value = oField.Value

 
   if(IsNull(oField.Value)) then

     GetFieldValue="NULL"
     exit function

   end if

   ' we need to put quotes around anything like a string requiring quotes,
   ' and double up any exisiting quotes in the name

   if oField.Type = adChar OR oField.Type = adVarChar OR oField.Type = adDate then
     value = Replace(value, "'", "''")
     value = "'" + value + "'"     
   end if

   ' bool values want to marshal as "true" and "false" which SQL doesnt like
   if oField.Type = adBoolean then
     if CBool(value) = true then
        value = 1
     else
        value = 0
     end if
   end if

   GetFieldValue = CStr(value)
End Function