Loop over SQL commands in a file

Question

I have an SQL file that looks like this (clearly the real thing is a bit longer and actualy does stuff :))

DECLARE @Mandatory int = 0 
DECLARE @Fish int = 3 

DECLARE @InitialPriceID int
if @Mandatory= 0
    begin
    select @InitialPriceID = priceID from Fishes where FishID = @Fish
    end

I have a file of 'Mandatory' and 'Fish' values

  Mandatory,Fish
     1,3
     0,4
     1,4
     1,3
     1,7

I need to write a program that will produce an SQL file (or files) for our DBO to run against the database. but I am not quite sure how to approach the problem...

Cheers

You could import the file using SQL Server's import wizard, then script the values to wherever you need them afterwards. — Bridge, Aug 15 '12 at 08:19
As Bridge said, or alternatively you can use a scripting language (Bash/PHP/Ruby/Python or anything you're familiar with) that will read the list, generate the file, run the file and dispose of it. — Bart Platak, Aug 15 '12 at 08:20

score 1 · Answer 1 · answered Aug 15 '12 at 08:22

You should generally prefer set based solutions. I've no idea what the full solution would look like, but from the start you've given:

declare @Values table (Mandatory int,Fish int)
insert into @Values(Mandatory,Fish) values
(1,3),
(0,4),
(1,4),
(1,3),
(1,7),

;with Prices as (
    select
        Mandatory,
        Fish,
        CASE
            WHEN Mandatory = 0 THEN f.PriceID
            ELSE 55 /* Calculation for Mandatory = 1? */
        END as InitialPriceID
    from
        @Values v
            left join /* Or inner join? */
        Fishes f
            on
                v.Fish = f.Fish
) select * from Prices

You should aim to compute all of the results in one go, rather than trying to "loop through" each calculation. SQL works better this way.

score 1 · Answer 2 · answered Aug 15 '12 at 08:25

At the risk of over-simplifying things in C# or similar you could use a string processing approach:

class Program
{
    static void Main(string[] args)
    {
        var sb = new StringBuilder();

        foreach(var line in File.ReadLines(@"c:\myfile.csv"))
        {
            string[] values = line.Split(',');

            int mandatory = Int32.Parse(values[0]);
            int fish = Int32.Parse(values[1]);

            sb.AppendLine(new Foo(mandatory, fish).ToString());
        }

        File.WriteAllText("@c:\myfile.sql", sb.ToString());
    }

    private sealed class Foo
    {
        public Foo(int mandatory, int fish)
        {
            this.Mandatory = mandatory;
            this.Fish = fish;
        }

        public int Mandatory { get; private set; }
        public int Fish { get; set; }

        public override string ToString()
        {
            return String.Format(@"DECLARE @Mandatory int = {0}
DECLARE @Fish int = {1}

DECLARE @InitialPriceID int
if @Mandatory= 
begin
select @InitialPriceID = priceID from Fishes where FishID = @Fish
end
", this.Mandatory, this.Fish);
        }
    }
}

score 1 · Answer 3 · edited May 23 '17 at 12:04

1

There are many article on how to read from a text file through t-sql, check "Stored Procedure to Open and Read a text file" on SO and if you can make change the format of you input files into xml, then you can check SQL SERVER – Simple Example of Reading XML File Using T-SQL

edited May 23 '17 at 12:04

Community

1
1

answered Aug 15 '12 at 08:25

Vamsi

4,237
7
49
74

Loop over SQL commands in a file

3 Answers3