Run a script multiple times based on lines in a file

[simonsabin]

Loading data can be a pain, especially if you want to run a script for every piece of data.

Currently you have to do that in the scripting shell, this means that

• you can't have the script run as a transaction
• you have to read the file and map each value to a variable on the command line
• paralleism is hard in and would be great to be dooable in SQLCMD
• running multiple sqlcmd results in the overhead of connections each time, this would improve that
• It provides a richer data loading experience where bulk insert isn't an option

Proposal would be to do something like

` sqlcmd -mf MyVars.json -Q "CREATE LOGIN [$(User)] FROM WINDOWS';
GO

-- Creates a database user for the login created above.
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem; `

when MyVars.json is

[
  { "User":"user1", "Schema":"myUser"},
  { "User":"User2", "Schema":"Schema2"}
]

is effectively the same as

sqlcmd -v User=user1 -v Schema=myUser
sqlcmd -v User=user2 -v Schema=Schema2

A similar experience could be achieved with a CSV file.