Search code examples
sqlsql-serverdatetimessissql-server-2014

A date time field precise enough to differentiate between rows on bulk/mass insert

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.

Solution

  • As described in this answer, you should use timestamp.

    See documentation here or additional details available here.

    Hope this help.