Htykuut

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

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.

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) );";

}

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().