問題描述

我有一個(gè)腳本將幾十萬條記錄插入到一??個(gè)表中.其中一列是 DATETIME2(7)，我將 SYSUTCDATETIME() 插入其中.問題是每條記錄都具有相同的確切時(shí)間戳.我試圖避免執(zhí)行游標(biāo)或 while 循環(huán)，而只是執(zhí)行一個(gè)不錯(cuò)的快速插入/選擇語句.

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.

有沒有辦法在直線插入中增加時(shí)間，即使是納秒?例如

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];

推薦答案

這些函數(shù)有時(shí)被稱為運(yùn)行時(shí)常量.查詢中對(duì)函數(shù)的每個(gè)引用在運(yùn)行時(shí)都會(huì)評(píng)估一次，但在整個(gè)查詢執(zhí)行過程中，每一行都獲得相同的值.SQL Server 認(rèn)為這是一個(gè)特性而不是一個(gè)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.

你能做什么?那么第一件事就是不要依賴時(shí)間戳來進(jìn)行這種區(qū)分.使用 identity 列來唯一標(biāo)識(shí)每一行.那么，這將不是問題.

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.

如果出于某種原因，您確實(shí)必須使用日期/時(shí)間列，那么您可以創(chuàng)建自己的常量.例如:

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()

時(shí)間不準(zhǔn)確.但我們?cè)谶@里談?wù)摰氖俏⒚?你可以使用納秒).

The timing isn't accurate. But we are talking microseconds here (and you could use nanoseconds).

這確實(shí)做出了關(guān)鍵假設(shè):

This does make key assumptions:

• 您沒有時(shí)間可能重疊的并發(fā)插入.
• 您沒有及時(shí)執(zhí)行插入操作，以免出現(xiàn)重疊.

我有沒有提到您應(yīng)該使用 identity 列代替?

Did I mention that you should be using an identity column instead?

這篇關(guān)于使用遞增的時(shí)間戳執(zhí)行插入的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！