使用脚本工具自动创建sql语句

发布于 2024-12-10 15:56:20 字数 823 浏览 0 评论 0原文

我经常有一个类似这样的任务：将大量用户插入到具有相似属性的用户表中。并不总是那么简单，但一般来说，字符串列表 ->对应的sql语句列表。

我通常的解决方案是使用 Excel 中的用户名列表使用公式生成大量插入语句

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

，然后填写公式以获取所有插入行。

这可行，但有时语句很长，有时每个步骤都包含几个不同的步骤，将其全部塞入 Excel 公式中并正确处理所有引号是一件痛苦的事情。

我想知道是否有更好的方法。我真正想要的是能够有一个模板文件 template txt:

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



          )
   values('<template tag1>...

然后使用一些神奇的命令行工具，只需能够输入类似的内容

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

，这给了我一个大文件，其中为每个用户名值重复模板标签替换为用户名。

那么这样的命令是否存在，还是我对命令行工具的期望太高了？任何免费的 Windows 工具都可以。我可以在不需要太多时间的情况下编写 c# 程序来完成此操作，但我觉得肯定已经有一个易于使用的工具了。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

墨离汐 2024-12-17 15:56:20

使用 Powershell 脚本这很简单。 PS 允许字符串中的内联变量，因此您可以执行以下操作：

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

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

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

@"...."@ 是 here-string，这使得编写可读代码变得非常容易，而无需转义引号等。

可以很容易地修改上面的内容，以接受各种标签的参数和输出文件的另一个参数，或者运行位于另一个 .txt 或 .csv 文件中的一组值作为输入。

编辑：

要修改它以接受参数，您只需在顶部添加一个param()块：

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

然后在脚本中使用这些 $variables：

set-content "$outfile" -value $SQLHS

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

回复收藏 0 原文

~没有更多了~