使用递增的时间戳执行插入
我有一个脚本将几十万条记录插入到一个表中.其中一列是 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?
相关文章