Date in SELECT statement is always the same

Question

I'm trying to insert data from one table into a table that has a two-column key. The source table does not share the destination's keys. Both key columns in the destination are varchars.

I have an insert statement:

INSERT INTO Table1 (Invoice, DetailLine, SomeData1, SomeData2) 
SELECT ('1'+RIGHT('00000000000000' + CONVERT(varchar, DatePart(ns,SYSDATETIME()), 14), 0, 'STARTING_VALUE_1407', [ActualValue] 
FROM Table2;

When I execute the above, my milliseconds for my DateTime2 object are all the same, as if it's only evaluating that value once. This is preventing me from using this as a temporary unique key. Is it possible to use SYSDATETIME(), or any other date function, in a SELECT statement and have the value reevaluated for each row? If not, is there a way to generate a unique value when doing an INSERT INTO SELECT when selecting data that doesn't normally share the destination table's key?

Have a look at column type `TIMESTAMP`: http://technet.microsoft.com/en-us/library/aa260631(v=sql.80).aspx — Joël Salamin, Sep 23 '14 at 14:43
if you need to generate unique value for each row - you can use GUIDs via `newid()` — Andrey Korneyev, Sep 23 '14 at 14:48
You could first insert it to a temp table that has Invoice as an IDENTITY() column, and then insert from the temp table to Table1. — Tab Alleman, Sep 23 '14 at 14:48
[Please don't use `varchar` without length](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx) and please don't include date/time data as part of a unique key. — Aaron Bertrand, Sep 23 '14 at 15:20
Also, [don't use cryptic shorthand like `ns`](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx). How much longer would it take you to write `NANOSECOND`? About 15 nanoseconds? Just do it, and make your code self-documenting instead of [maximizing productivity through shorthand](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-wrong-optimizations/#opt6). — Aaron Bertrand, Sep 23 '14 at 15:25

score 6 · Accepted Answer · edited Sep 23 '14 at 15:23

The SYSDATETIME() function is evaluated once in your query, because it is considered a runtime constant.

You can try a windowing function such as ROW_NUMBER():

INSERT INTO table1 
         (invoice, 
          detailline, 
          somedata1, 
          somedata2) 
SELECT ROW_NUMBER() 
         OVER (ORDER BY actualvalue), 
       0, 
       'STARTING_VALUE_1407', 
       [actualvalue] 
FROM   table2;

score 1 · Answer 2 · edited Sep 23 '14 at 15:28

1

I'm not sure if you have a requirement to be tied to milliseconds or if the values need to be the same in the same row, but the following could be used to get unique:

SELECT NEWID() AS GuidNo1, 
       NEWID() AS GuidNo2

Or

SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber1], 
       CAST(RAND() * 1000000 AS INT) AS [RandomNumber2]

edited Sep 23 '14 at 15:28

Kermit

33,827
13
85
121

answered Sep 23 '14 at 14:55

Tristan

279
5
21

Date in SELECT statement is always the same

2 Answers2