Explicit Field Declaration Vs Loop (VBScript to SQL Insert

Question

I have a basic question regarding the streamlining of VBScript/SQL code. I have a working script that pulls data from one table, parses it by delimiter '|', and reinserts it to a second 254 column table. In doing so, I explicitly wrote out each field for the insert, and each part of the array for values. Obviously, this isn't very efficient. To streamline this, I've been trying to loop through the array, insert the resulting row, and move to the next, like so:

'assume declaration of recordset, SQL connection, pull from first table, ect.
if recordset.EOF then
wscript.echo "No new records found."
else 

Do While NOT Recordset.EOF
dump = recordset("data")
if dump <> "" then
dump = Replace(dump,"'","")
cleanup=split(field,"|")

if instr(cleanup(0),"RECORD") then
for i = 1 to 254
cleanup(i)= cleanup(i) & "','"
i= i + 1
next

end if

strRecsql="INSERT INTO records_fields VALUES ('" & cleanup & "')"
connection.execute strRecsql

Now, when I explicitly stated all the fields and values, I ironed out all the datatype mismatch issues. Now that I'm looping through, I'm getting the datatype mismatch error, which I assume (always dangerous) means the script is reaching the insert point and failing.

My long code included things like cleanup(1) & "," & cleanup(2) & "," ''and so on Obviously, I'm doing something wrong in trying to loop through-- I'm just stumped because of the datatype mismatch.

Any insight into this issue would be very much appreciated. I'd love to have a code that works AND is efficient, and I'm just curious in general about datatype exchange between VBScript and SQL. I've found some excellent resources on the variant type and SQL types in general, but nothing that addresses the exchange.

Sample dump value:

"RECORD|2-10-15 00:00:00|SMITH|JOHN|+14.59|STAPLES| | | | | |AE15679|j2700| | |Office supplies| | |"

Mysql and sql server are 2 different products. Which one do you use? — Shadow
The cleanup variable is an array. You cannot simply concatenate an array with a string. You need to flatren the array back to a string using join() function. — Shadow
@Shadow -- Sorry about the mis-tagging, there. I definitely don't need to muddy the waters by mixing codes. Thank you for the tip! It at least puts me on the right track. I have a join in there now, but I seem to have run into another wall with an out of range string. I guess I wasn't dealing with this before because I was individually coding each part of the array into a string? Just trying to wrap my head around this. Thanks again! — atomickiwi
@Susilo -- a typical dump value looks like this: "RECORD|2-10-15 00:00:00|SMITH|JOHN|+14.59|STAPLES| | | | | |AE15679|j2700| | |Office supplies| | |" ... and so on, for 254 columns. A lot of them are empty, which didn't actually give me a problem when I was writing out the entire array input by hand. — atomickiwi

Susilo Susilo · Accepted Answer · 2016-02-16T06:18:35

OK, because you not reply my last comment, I have to make many assumption and make this answer primarily based on sample value

RECORD|2-10-15 00:00:00|SMITH|JOHN|+14.59|STAPLES| | | | | |AE15679|j2700| | |Office supplies| | |

Based on that sample its turn out that you not need to split the dump value because directly replace | with ', ' will solve the problem. My answer bellow is simplified so will not show all code but just a part needed

dump = "RECORD|2-10-15 00:00:00|SMITH|JOHN|+14.59|STAPLES|" & 
       "| | | | |AE15679|j2700| | |Office supplies| | |"

maxLen = 1000

if Left(dump, 7) = "RECORD|" then
    dump = "INSERT INTO records_fields VALUES ('" & 
           Replace(Mid(dump, 8, maxLen), "|","', '") & "')"

    dump = Replace(dump, "' '", "''") ' Replace ' ' with '' if needed.'
    dump = Replace(dump, "''''", "''") ' Replace '''' with '' if needed.'
end if

MsgBox dump

Output:

INSERT INTO records_fields VALUES ('2-10-15 00:00:00', 'SMITH', 'JOHN', '+14.59', 'STAPLES', '', '', '', '', '', 'AE15679', 'j2700', '', '', 'Office supplies', '', '', '')

Explicit Field Declaration Vs Loop (VBScript to SQL Insert

1 Answers