OdeToCode IC Logo

INSERT statements for your SQL database

Friday, June 4, 2004

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
---