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