0

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];
Dale K
  • 25,246
  • 15
  • 42
  • 71
PKD
  • 685
  • 1
  • 13
  • 37
  • That absolutely did the trick, and without loops or row counters, or anything. Thanks so much! – PKD Mar 11 '21 at 16:06

1 Answers1

1

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.

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:

  • You don't have concurrent inserts where the times can be overlapping.
  • You are not running inserts so close in time that you might get overlaps.

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The tables already have an identity column. What I need is a mass insert into the table, but with incremented timestamps on the rows. @ItalianStallion4215 had it exactly right. – PKD Mar 11 '21 at 16:08