CODEWITHSUNDEEP
c#sqlsql-serverloops
Matt
Matt

Reputation: 15061

C# SQL loop in insert statement from string values

I have an insert statement which creates a new row into a table:

string SQLQuery = "INSERT INTO TABLEABC VALUES('" + Rtext + "', '" + TDate + "')";

But I want to create multiple lines for 20 repeats worth of data for 8 week (56 days) intervals.

For example:

Rtext TDate
John  23/11/2018

I would want it to create:

Rtext TDate
John  23/11/2018
John  18/01/2019
John  15/03/2019
John  10/05/2019
John  05/07/2019
John  30/08/2019
John  25/10/2019
John  20/12/2019
John  14/02/2020
John  10/04/2020
John  05/06/2020
John  31/07/2020
John  25/09/2020
John  20/11/2020
John  15/01/2021
John  12/03/2021
John  07/05/2021
John  02/07/2021
John  27/08/2021
John  22/10/2021
John  17/12/2021

Upvotes: 0

Views: 2177

Answers (4)

Tetsuya Yamamoto
Tetsuya Yamamoto

Reputation: 24957

I preferred using SQL parameters and a for loop to execute the insert command for every iteration, but you should clear parameters before next execution begins. Here is an example by assumed that you're converting the date string to DateTime first:

string SQLQuery = "INSERT INTO TABLEABC (RText, TDate) VALUES (@RText, @TDate)";

// edit: TDate is a string, convert it to DateTime first
DateTime date;

if (DateTime.TryParseExact(TDate, "dd/MM/yyyy", CultureInfo.InvariantCulture, 
    DateTimeStyles.None, out date)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Open();
        using (var cmd = new SqlCommand(SQLQuery, con))
        {
            for (var i = 0; i < 20; i++)
            {
                cmd.Parameters.AddWithValue("@RText", Rtext);

                // add for next iterations
                if (i > 0)
                {
                    date = date.AddDays(56);
                }

                cmd.Parameters.AddWithValue("@TDate", date);

                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear(); // clear existing parameters for next iteration
            }
        }
    }
}
else
{
    // handle invalid dates
}

Note: If the data type is exactly known, use Parameters.Add() instead of Parameters.AddWithValue().

Upvotes: 1

Thom A
Thom A

Reputation: 95949

Firstly, you should be parametrising your query. My C# is very rusty (poor), so you'll have to excuse me if this is wrong, however, if I recall (and my Google-fu worked), then you'll want to do something more like:

string SQLQuery = "INSERT INTO TABLEABC VALUES(@RText, @TDate)";
SQLCommand Command = new SQLCommand(SQLQuery, YourConnection);
Command.Parameters.Add("@RText",SQLDbType.varchar);
Command.Parameters["@RText"].Value = RText; //Assumed variable name
Command.Parameters.Add("@TDate",SQLDbType.date); //Assumed datatype
Command.Parameters["@TDate"].Value = TDate; //Assumed variable name

This doesn't solve the repeating items though, so the first line could be replaced with:

string SQLQuery = "INSERT INTO TableABC SELECT @RText, DATEADD(WEEK, 8*I, @TDate) FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19)) V(I);";

Alternativey, you could use a rCTE instead of a virtual Tally table. In SQL this would look like:

WITH rCTE AS(
        SELECT @RText AS RText,
               @TDate AS TDate
               0 AS I
        UNION ALL
        SELECT RText,
               DATEADD(WEEK, (I+1)*8,TDate) AS TDate,
               I+1
        FROM rCTE
        WHERE I+1 <20
)
INSERT INTO TABLEABC
SELECT RText,
       TDate
FROM rCTE;

If you're going to have a large amount of values, a scalable Tally Table is the way to go:

WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1 -10
         CROSS JOIN N N2 --100
         CROSS JOIN N N2 --1000
         CROSS JOIN N N2 --10000 --you get the idea
    )
INSERT INTO TABLEABC
SELECT TOP 500
       @RText,
       DATEADD(WEEK, (I-1)*8, @TDate)
FROM Tally;

A rCTE, in the sense above, is an RBAR method, so it's speed will get slower the more rows you need. A tally table would be far faster, isn't RBAR, and doesn't need the MAXRECURSION option.

Upvotes: 2

Agustus Codes
Agustus Codes

Reputation: 59

Looking into the syntax you are using, seems like you are trying it in C#. So use below code.

you need to use stringbuilder and append insert string or concate in SQLQuery. Use below code. 

Datetime myDate = Convert.toDatetime("23/11/2018")
string SQLQuery = "INSERT INTO TABLEABC VALUES('" + Rtext + "', '" + TDate + "');";
SQLQuery += "INSERT INTO TABLEABC VALUES('" + John  + "', '" + myDate + "');";

for (int i = 1; i <=19; i++)
{
      SQLQuery +=" INSERT INTO TABLEABC VALUES('" + John  + "', myDate.AddDays(56) );";
}

Upvotes: 1

Dumi
Dumi

Reputation: 1444

You can try this.

DECLARE @i INT=0, @LastDate AS DATE=GETDATE()

WHILE (@i < 20)
BEGIN
    INSERT INTO TABLEABC VALUES('" + Rtext + "', '" + @LastDate + "')

    SET @LastDate = @LastDate.AddDays(56)
    SET @i=@i+1;
END

Upvotes: 0

Related Questions