1

I am using SSIS to insert 500 to 3+ million rows into various tables. The data source is anything from a flat CSV file to another DB (Oracle, MySQL, SQL Server).

I am trying to create an "inserted_on" column that shows the date/time stamp of when the row was added and I need it to be precise enough to differentiate between the previous and next row. In other words, every row should have a different date time value, even if its really close.

I tried a datetime2(7) field with a default value of (gettime()) but that doesn't seem precise enough.

IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • I think if you want to guarantee you know the order of the inserts, you should use some sort of growing ID field to represent row inserts rather than relying on time. As you seem to have noted, the maximum precision for datetime2 isn't necessarily going to be unique. – ZLK Dec 13 '17 at 02:42
  • 1
    Use `SYSDATETIME()` instead of `GETDATE()` for sub-millisecond precision. However. you still might need an incremental value to break ties. – Dan Guzman Dec 13 '17 at 03:31

1 Answers1

2

As described in this answer, you should use timestamp.

See documentation here or additional details available here.

Hope this help.

ViKiNG
  • 1,294
  • 2
  • 19
  • 26
  • Note: In SQL Server, `CURRENT_TIMESTAMP` is of the datatype `DATETIME` and is not related to `TIMESTAMP` (which is the equivalent of `ROWVERSION` and not related to an actual time stamp). The documentation on `ROWVERSION` also notes that `TIMESTAMP` will be deprecated, for what it's worth. – ZLK Dec 13 '17 at 03:36
  • Thanks for the updates @ZLK. Appreciated! the deprecated news is alarming for me as I'm widely using this with SSIS imports. – ViKiNG Dec 13 '17 at 03:39
  • So now I am wondering should I use a growing ID or `timestamp`/`rowversion`? – IMTheNachoMan Dec 13 '17 at 14:15
  • That is a case based judgment call. We are using `timestamp` without any hassle where the daily extract is in hundreds of thousands of transactions. and the order is important. – ViKiNG Dec 13 '17 at 19:40