Filling table with datetime's incremented by one second each

Question

I have a MyDatabase.MyTable.DateCol with few thousand rows, which I want to fill up with datetime. I want each date to be bigger than the previous one by 1 second. How can I do that?

Answer 1

Sample Table

CREATE Table DateTable 
(ID INT IDENTITY(1,1),Name NVARCHAR(300), Data Datetime)
GO

Test Data

INSERT INTO DateTable (Name)
VALUES ('John'),('Mark'),('Phil'),('Simon'),('Sam'),('Pete'),('Josh')
GO

Query

;WITH CTE
AS
(
SELECT *, rn = ROW_NUMBER() OVER (ORDER BY ID ASC)  FROM DateTable
)
UPDATE CTE
SET  Data = DATEADD(SECOND, CTE.rn, GETDATE())

Result Set

SELECT * FROM DateTable

ID  Name    Data
1   John    2013-11-06 20:34:59.310
2   Mark    2013-11-06 20:35:00.310
3   Phil    2013-11-06 20:35:01.310
4   Simon   2013-11-06 20:35:02.310
5   Sam     2013-11-06 20:35:03.310
6   Pete    2013-11-06 20:35:04.310
7   Josh    2013-11-06 20:35:05.310