問題描述
我有一個腳本將幾十萬條記錄插入到一??個表中.其中一列是 DATETIME2(7)，我將 SYSUTCDATETIME() 插入其中.問題是每條記錄都具有相同的確切時間戳.我試圖避免執行游標或 while 循環，而只是執行一個不錯的快速插入/選擇語句.
I have a script inserting a few hundred thousand records into a table. One of the columns is a DATETIME2(7), and I'm inserting SYSUTCDATETIME() into it. The problem is that every single record has the same exact time stamp. I'm trying to avoid doing a cursor or while loop, and just perform a nice fast insert into/select from kind of statement.
有沒有辦法在直線插入中增加時間，即使是納秒?例如
Is there a way to have the time increment, even it it's by nano seconds, in a straight insert? e.g.
INSERT INTO [dbo].[Users]
( [FirstName]
, [LastName]
, [CreatedDate] )
SELECT [DI].[FirstName] AS [FirstName]
, [DI].[LastName] AS [LastName]
, SYSUTCDATETIME() AS [CreatedDate]
FROM [dbo].[DataImport] AS [DI];
推薦答案
這些函數有時被稱為運行時常量.查詢中對函數的每個引用在運行時都會評估一次，但在整個查詢執行過程中，每一行都獲得相同的值.SQL Server 認為這是一個特性而不是一個bug.
These functions are sometimes referred to as runtime constants. Each reference to the function in the query is evaluated once at runtime, but each row gets the same value fro the entire query execution. SQL Server considers this a feature not a bug.
你能做什么?那么第一件事就是不要依賴時間戳來進行這種區分.使用 identity 列來唯一標識每一行.那么，這將不是問題.
What can you do? Well the first thing is to simply not rely on a timestamp for this differentiation. Use an identity column to uniquely identify each row. Then, this won't be an issue.
如果出于某種原因，您確實必須使用日期/時間列，那么您可以創建自己的常量.例如:
If, for some reason, you do have to use the date/time column, then you can make up your own constant. For instance:
dateadd(microsecond, row_number() over (order by (select null)), SYSUTCDATETIME()
時間不準確.但我們在這里談論的是微秒(你可以使用納秒).
The timing isn't accurate. But we are talking microseconds here (and you could use nanoseconds).
這確實做出了關鍵假設:
This does make key assumptions:
- 您沒有時間可能重疊的并發插入.
- 您沒有及時執行插入操作，以免出現重疊.
我有沒有提到您應該使用 identity 列代替?
Did I mention that you should be using an identity column instead?
這篇關于使用遞增的時間戳執行插入的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！