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.
mrongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

0
mrongAuthor Commented:
Yes.
0
RobSampsonCommented:
Try out the VBS code, then have a look in Query.SQL to see if it's right.

Rob.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mrongAuthor Commented:
TBL1
TBL2
are from TBL.txt file.

thanks
0
RobSampsonCommented:
OK, try this.

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

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTables = objFSO.OpenTextFile(strTables, 1, False)
arrTables = Split(objTables.ReadAll, vbCrLf)
objTables.Close
Set objInput = objFSO.OpenTextFile(strInput, 1, False)
Set objOutput = objFSO.CreateTextFile(strOutput, True)
While Not objInput.AtEndOfStream
	strLine = objInput.ReadLine
	arrNames = Split(strLine, " ")
	For Each strTable In arrTables
		If Trim(strTable) <> "" Then
			strStatement = "Update " & Trim(strTable) & " SET Name='" & arrNames(0) & "' WHERE Name='" & strNames(UBound(arrNames)) & "';"
			objOutput.WriteLine strStatement
		End If
	Next
Wend
objInput.Close
objOutput.Close
WScript.Echo "Done"

Open in new window


Regards,

Rob.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrongAuthor Commented:
Got the error message below.

Type mismatch: 'strNames'
0
mrongAuthor Commented:
changed to the following and works.

Do While Instr(strLine, "  ") > 0
         strLine = Replace(strLine, "  ", " ")
         Loop
      arrNames = Split(strLine, " ")
        strColumn1 = arrNames(0)
        strColumn2 = arrNames(1)
0
RobSampsonCommented:
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.
0
mrongAuthor Commented:
Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.