Adding milliseconds to a DateTime in TSQL INSERT INTO

I'd agree with Mark Byers' answer as the real solution. Just wanted to add a gotcha, that prior to SQL Server 2008, the datetime accuracy is to about 3.33ms. Quote from MSDN:

datetime values are rounded to increments of .000, .003, or .007 second...

So adding 1ms to dates will not solve your problem.

e.g.

SELECT DATEADD(ms, 1, '2010-04-12T12:00:00.000') -- outputs time still as x.000s
SELECT DATEADD(ms, 2, '2010-04-12T12:00:00.000') -- output: .003s
SELECT DATEADD(ms, 3, '2010-04-12T12:00:00.000') -- output: .003s
SELECT DATEADD(ms, 4, '2010-04-12T12:00:00.000') -- output: .003s
SELECT DATEADD(ms, 5, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 6, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 7, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 8, '2010-04-12T12:00:00.000') -- output: .007s
SELECT DATEADD(ms, 9, '2010-04-12T12:00:00.000') -- output: .010s

You'd actually be needed to add 3ms each time. At best it would work for your situation, but just not really be what feels like a "clean" solution, a bit of hack. At worst, it just wouldn't work/scale, depending on the data volumes/the density of the data spread. But, you should be aware of the datetime accuracy gotcha if you head down this route.

SQL Server 2008 does introduce DATETIME2 which has an accuracy of 100ns. See DaveK's answer.

I think the real problem is that RFQ_ID, Action_Time shouldn't be a primary key. Create a surrogate primary key and put a non-unique index on RFQ_ID, Action_Time.

Update: If you really want to stick with your existing design you could do what you asked but using 10 milliseconds instead of one millisecond between each row, to compensate for the low precision of datetime. You can use the row number to determine how many milliseconds to add so that you get a different timestamp for each row:

INSERT INTO QSW_RFQ_Log
(RFQ_ID, Action_Time, Quote_ID, Note)
SELECT
  RFQ_ID,
  DATEADD(millisecond, 10 * ROW_NUMBER() OVER (ORDER BY Quote_ID), GETDATE()) AS Action_Time,
  Quote_ID,
  'Added to RFQ on Initialization' AS Note
FROM QSW_Quote