将毫秒添加到 TSQL INSERT INTO 中的日期时间 [英] Adding milliseconds to a DateTime in TSQL INSERT INTO

问题描述

我正在执行 INSERT INTO 查询以初始化新表.主键是 RFQ_ID 和 Action_Time

I'm doing an INSERT INTO query in order to initialize a new table. The primary key is RFQ_ID and Action_Time

如何为新记录的每个 Action_Time 添加 1 毫秒以避免违反 PRIMARY KEY 约束"

How could add 1 millisecond to each Action_Time on a new record in order to avoid "Violation of PRIMARY KEY constraint"

INSERT INTO QSW_RFQ_Log
            (RFQ_ID, Action_Time, Quote_ID)
SELECT     RFQ_ID, GETDATE() AS Action_Time,  Quote_ID, 'Added to RFQ on Initialization' AS Note
FROM         QSW_Quote

推荐答案

我认为真正的问题是 RFQ_ID, Action_Time 不应该是主键.创建一个 surrogate 主键，并在 RFQ_ID, Action_Time 上放置一个非唯一索引代码>.

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.

更新:如果你真的想坚持你现有的设计，你可以按照你的要求做，但在每行之间使用 10 毫秒而不是 1 毫秒，以补偿日期时间的低精度.您可以使用行号来确​​定要添加多少毫秒，以便为每一行获得不同的时间戳:

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

这篇关于将毫秒添加到 TSQL INSERT INTO 中的日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！