asked on

VBScript generate update SQL stmt

Greeting,

I have an input.txt with two columns and the following sample data. the empty strings between two columns are unknown.

input.txt
TOM JOHN
STEVE DAVE

Also I have TBL.txt with the following data
TBL1
TBL2

How to create a vbscript which will write to update.sql with the following output?
Update TBL1 set name='JOHN' where name='TOM';
Update TBL2 set name='JOHN' where name='TOM';
Update TBL1 set name='DAVE' where name='JOHN';
Update TBL2 set name='DAVE' where name='JOHN';

Please provide sample.
Thanks.

RobSampson

Hi, should the second two statements be
Update TBL1 set name='DAVE' where name='STEVE';
Update TBL2 set name='DAVE' where name='STEVE';

I think this should work for you.

Regards,

Rob.

strInput = "file.txt"
strOutput = "Query.SQL"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objInput = objFSO.OpenTextFile(strInput, 1, False)
Set objOutput = objFSO.CreateTextFile(strOutput, True)
While Not objInput.AtEndOfStream
	strLine = objInput.ReadLine
	arrNames = Split(strLine, " ")
	strStatement = "Update TBL1 SET Name='" & arrNames(0) & "' WHERE Name='" & strNames(UBound(arrNames)) & "';"
	objOutput.WriteLine strStatement
	strStatement = "Update TBL2 SET Name='" & arrNames(0) & "' WHERE Name='" & strNames(UBound(arrNames)) & "';"
	objOutput.WriteLine strStatement
Wend
objInput.Close
objOutput.Close
WScript.Echo "Done"

Open in new window

mrong

ASKER

Yes.

RobSampson

Try out the VBS code, then have a look in Query.SQL to see if it's right.

Rob.

mrong

ASKER

TBL1
TBL2
are from TBL.txt file.

thanks

ASKER CERTIFIED SOLUTION

RobSampson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrong

ASKER

Got the error message below.

Type mismatch: 'strNames'

mrong

ASKER

changed to the following and works.

Do While Instr(strLine, " ") > 0
strLine = Replace(strLine, " ", " ")
Loop
arrNames = Split(strLine, " ")
strColumn1 = arrNames(0)
strColumn2 = arrNames(1)

RobSampson

Nice one. I was wondering whether it was going to work with varying spaces, but it looks like you did the right thing there.

Thanks for the grade,

Rob.

mrong

ASKER

Thank you.