• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

VB script

Greeting,

I have a txt file and its col1 contains a list of values.
I also have the following sql.

select count(*)
from TBL
where bldg in(xxx);

I need a script to append col1 to xxx and seperate by comma)

Thanks in advance.
0
mrong
Asked:
mrong
  • 4
  • 4
1 Solution
 
x-menIT super heroCommented:
you can use powershell to "build" the SQL script:

PS>"select count(*) from TBL where bldg in (xxx" > SQLscript.sql
PS> gc col1.txt | % {write-host -nonewline ",$_" >> SQLscript.sql}
PS> ")" >> SQLscript.sql
0
 
mrongAuthor Commented:
what changes need to be done if I want to sub TBL with col1 in another txt?
thanks.
0
 
mrongAuthor Commented:
x-men,

I tried your suggestion and got the SQLscript.sql with the following data. It didn't sub xxx with col1.txt

select count(*) from TBL where bldg in (xxx
)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
x-menIT super heroCommented:
col1.txt should have one value per row. The command  "PS> gc col1.txt" (Get-Content)  should return the contents of the file.


PS>"select count(*) from TBL where bldg in (" > SQLscript.sql
PS> gc col1.txt | % {write-host -nonewline ",$_" >> SQLscript.sql}
PS> ")" >> SQLscript.sql
0
 
mrongAuthor Commented:
Yes, col1.txt has one value per row and  the command  "PS> gc col1.txt" (Get-Content)  did returned the contents of the file. but it didn't put the values into SQLscript.sql.
0
 
x-menIT super heroCommented:
yes, sorry:

 gc col1.txt | % {$x=$x+",$_"};$x >> SQLscript.sql}
0
 
mrongAuthor Commented:
So the complete code should be the followings? thx

PS>"select count(*) from TBL where bldg in (" > SQLscript.sql
PS> gc col1.txt | % {$x=$x+",$_"};$x >> SQLscript.sql}
PS> ")" >> SQLscript.sql
0
 
x-menIT super heroCommented:
yes, note that there will be a "," that needs to be deleted.

"...TBL where bldg in (,ABC,SDF,..."
                                         ^
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now