How to loop in Sql Server and update a certain column?

Question

I have records in my Sql Server 2008 table, I need to update the field TranTime. How can I do it using a loop? Evey iteration should insert/update a unique time.

I don't know the syntax/approach. Any help would be appreciated.

you can loop using cursor but its not advisable! So one possible solution is to get all the affected records and perform an update query with the destination table and the results table, this is more efficient than cursor — Anonymous Duck, Mar 09 '16 at 09:53
yes but also consider other scenarios like if the there are millions of records in the database — Anonymous Duck, Mar 09 '16 at 09:56
Here is an example for a cursor based solution: http://stackoverflow.com/questions/11852782/t-sql-loop-over-query-results Here is an example for a solution using the T-SQL `while` method: http://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008 — Ralph, Mar 09 '16 at 09:58
I guess it will cause a slow performance or even a problem in the database. But in my records it's just 50 records. — Francis Saul, Mar 09 '16 at 09:59
@Ralph: Thanks for the reference, I might use the second reference. thanks! — Francis Saul, Mar 09 '16 at 10:02
Looping/Cursors are almost always the worst solution, SQL databases are designed for set based operations and if its possible to use one you almost always should. — Alex K., Mar 09 '16 at 11:13

Salah Akbari · Answer 1 · 2016-03-09T10:35:43.950

0

Update statement by using DateTime.Now:

SqlCommand cmd = new SqlCommand("Update yourTableName set TranTime = @dt",yourConnection);
SqlParameter parameter = cmd.Parameters.Add("@dt", System.Data.SqlDbType.DateTime);
parameter.Value = DateTime.Now;

If you want to update it by time only and unique you could use TimeOfDay. Like this:

DateTime.Now.TimeOfDay

Or ToLongTimeString like this:

DateTime.Now.ToLongTimeString()

Based on your needs.

edited Mar 09 '16 at 10:35

answered Mar 09 '16 at 09:55

Salah Akbari

39,330
10
79
109

Hi, I think it's a date stamp not a time. . .how to insert it by time stamp and unique, like increment by one every insert? thanks! – Francis Saul Mar 09 '16 at 09:58
@FrancisSaul...You could use `TimeOfDay`. Like this: `DateTime.Now.TimeOfDay` or `ToLongTimeString` like this: `DateTime.Now.ToLongTimeString()`. – Salah Akbari Mar 09 '16 at 10:01

score 0 · Accepted Answer · answered Mar 11 '16 at 05:11

I have come up with this solution

    DECLARE @ID INT
    DECLARE @T int
    DECLARE @Time time(7)
    SET @ID = 2
    set @T = 80005836 //Around 8:AM

    WHILE (@ID <=15) //I want to update 15 rows only
    BEGIN


   SELECT (@T / 1000000) % 100 as hour,
          (@T / 10000) % 100 as minute,
          (@T / 100) % 100 as second,
          (@T % 100) * 10 as millisecond


   SET @Time = 
   (
   SELECT dateadd(hour, (@T / 1000000) % 100,
   dateadd(minute, (@T / 10000) % 100,
   dateadd(second, (@T / 100) % 100,
   dateadd(millisecond, (@T % 100) * 10, cast('00:00:00' as time(2)))))) AS Time 
   )

     UPDATE myTable
     SET TranTime = @Time
     WHERE ID = @ID
    //increment ctr for ID and add 1 hour for a Time
    SET @ID = @ID + 1
    SET @T = @T + 1000000
    END
    GO

How to loop in Sql Server and update a certain column?

2 Answers2