C# SQL loop in insert statement from string values

while loop in sql select statement
while loop in sql server stored procedure
for loop in sql query example
sql while loop counter
sql server while loop example
sql while loop through table rows
for loop in sql server stored procedure
sql cursor

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

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

how to insert values of loop in database, i am using asp.net with c#. You don't want to do the database update inside the loop since this, I think, might cause an And then, put the values of the collection as parameters on an SqlDataCommand , and then do the database update. string query = "INSERT INTO YourTable (col1, col2, col3, etc. In my opinion, the best way is to create stored procedure. In stored procedure you should make a loop, which would insert data into table. From your C# application you open a connection to DB, call once a stored procedure and close a connection. On SQL you get best perfomance working with big amount of data. Here is an example

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.

SQL While loop: Understanding While loops in SQL Server, Let's now see how the SQL While loop is used to insert dummy In the body of the while loop, the INSERT query is being used to For the Name column, the value of the @count variable is appended with the string Car- . I am trying to create a SQL While loop that will update a temp table with values from another table. Values from the other table: 477286 560565 499330 391827 127375 526354 501736 357359 410433 500946 261297 377667 135931 235691 247239 143672 548752 471945 Wrote the following, however, it only inserts the last value multiple times over.

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

PHP MySQL Insert Multiple Records, Multiple SQL statements must be executed with the mysqli_multi_query() function​. The following examples add three new records to the "MyGuests" table:  Thanks for contributing an answer to Code Review Stack Exchange! Please be sure to answer the question. Provide details and share your research! But avoid … Asking for help, clarification, or responding to other answers. Making statements based on opinion; back them up with references or personal experience. Use MathJax to format equations.

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

JavaScript for Statement, Executed before the loop (the code block) starts. Normally this statement is used to initialize a counter variable. To initiate multiple values, separate each value  I have the following SQL statement: INSERT INTO Table1 (UserID, CustomerID, City, State) VALUES (???, 0, ‘Miami’,'Florida') I need a loop that will insert the following values for ??? (6578, 1546, 7865). I don't want to have to write this statement 3 times. Can someone please help?

Java Arrays, To declare an array, define the variable type with square brackets: String[] cars;. We have now declared a variable that holds an array of strings. To insert values  Pass the values in a string with ColumnSeperator and/or RowSeperator and pass it to Stored proc in SQL Server. Split it in proc and Insert Here what we can do is, from the front end create a string with values separated by predefined ColumnSeperator and/or RowSeperator and then let Stored Proc parse it, separate the data, bring the data into

Converting row values in a table to a single concatenated string , The article outlines six different ways of doing this utilising loops, the CLR, INSERT INTO ConcatenationDemo The requirement is to create a query that will concatenate the values in the 'Txt' SQL Server 2005 introduced the ability to write logic using procedural code in C# or other dot net languages using the CLR. In the body of the while loop, the INSERT query is being used to insert one record into the Cars table. For the Name column, the value of the @count variable is appended with the string Car- . For the Price column of the Cars table, the value of the @count variable is multiplied by 100.

Several Ways to Insert Split Delimited Strings in a Column – {coding , Split the string using STRING_SPLIT function and insert the output into a table. To do that, we need to execute the following query: STRING_SPLIT is a table-​valued function, introduced in SQL Server 2016. This variable holds the value of the separator, which will be used to split the strings into  Hello Experts, using vbscript i want to store data in MS SQL. i have prepared a script which works fine when i provide the static value but in actual i get the values in an array and now want to store values from there.

Comments
  • Recursive cte? Add 56 days for each iteration.
  • What's wrong with just using DateTime.AddDays(56) in a loop from 1 to 20?
  • Use TDate.AddDays(56) and assign parameters for every execution. Don't forget to use Parameters.Clear().
  • @jarlh liek this? DECLARE @i int = 0 WHILE @i < 300 BEGIN SET @i = @i + 1 /* your code*/ END
  • Why are you not parametrising your query either? This is wide open to SQL injection. Personally, I would handle the additional rows in SQL Server as well; either by use of an rCTE (as @jarlh mentioned) or a (virtual) tally table.
  • This won't work as is, you have to consider the fact that it is inside c# code. Double-check your quotation marks ;) Also this won't prevent SQL-injection. Plus, does SQL really support .AddDays(56)?
  • Looping is not great inside SQL Server, and this doesn't address to injection issue at all.
  • What does AddDays(56) do in SET @LastDate = @LastDate.AddDays(56)? is this a new function in SQL Server?
  • @Sami Initial value of '@LastDate is today's date. '@LastDate.AddDays(56) means we are adding 56days to the '@LastDate.
  • @ThilinaNakkawita, that's great, if the code you've got in your answer was in a language that supported that syntax. What you've got here is a weird hybrid of C# and SQL, which will run in neither