automate creating sql statements using scripting tool

Question

I often have a task a bit like this: insert a large number of users onto to the users table with similar properties. Not always that simple, but in general, list of strings -> list of corresponding sql statements.

my usual solution is this with the list of usernames in excel use a formula to generate a load of insert statements

=concatenate("insert into users values(username .......'",A1,"'.....

and then I fill down the formula to get all the insert rows.

This works but sometimes the statement is long, sometimes including a few different steps for each, and cramming it all into an excel formula and getting all the wrapping quotes right is a pain.

I'm wondering if there is a better way. What I really want is to be able to have a template file template txt:

insert into users
  ([username],
          [company] ...



          )
   values('<template tag1>...

and then using some magic command line tool, to simply be able to type something like

command_line> make_big_file_using_template template.txt /values [username1 username2] 
/output: bigfile.txt

and this gives me a big file with the template repeated for each username value with the tag replaced with the username.

So does such a command exist, or are my expectations of command line tools too high? Any freely available windows tool will do. I could whip up a c# program to do this in not too much time but I feel like there must be an easy to use tool out there already.

Where does template.txt come from? The database? What RDBMS are you using? Many have INSERT commands that can read form text files. — Dour High Arch
– Dour High Arch, Commented Oct 17, 2011 at 18:14
I wrote it. The question is independent of DB type, in fact this is a problem I have sometimes for generating other (non sql) types of code. — Jonny Cundall
– Jonny Cundall, Commented Oct 17, 2011 at 18:31
Why generate SQL this way and not just have a stored procedure which acts on data you import? — sorpigal
– sorpigal, Commented Oct 17, 2011 at 18:45

JNK · Accepted Answer · 2011-10-17 18:41:02Z

1

This is trivial using a Powershell script. PS allows inline variables in strings, so you could do something like:

$Tag1 = 'blah'
$Tag2 = 'foo'

$SQLHS = @"
INSERT INTO users
([username],
 [company],...)
VALUES
('$tag1', '$tag2'...)
"@

set-content 'C:\Mynewfile.txt' -value $SQLHS

The @"...."@ is a here-string, which makes it very easy to write readable code without escaping quotes and such.

The above could be very easily modified to accept parameters for the various tags and another for the output file, or to run for a set of values located in another .txt or .csv file as inputs.

EDIT:

To modify it to accept parameters, you can just add a param() block at top:

param($outfile, $tab1, $tab2, $tab3)

Then use those $variables in your script:

set-content "$outfile" -value $SQLHS

edited Oct 17, 2011 at 18:41

answered Oct 17, 2011 at 18:20

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonny Cundall Over a year ago

looks like just thing i wanted - but how do I modify it to take a list of values and output them all into a big file?

Collectives™ on Stack Overflow

automate creating sql statements using scripting tool

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related