Insert multiple rows as single statement

[sebastian-alfers]

I am trying to insert something like this:

insert into table(id, db_timestamp) values (1, SYSDATETIME()), (2,SYSDATETIME());

Looking at the two rows, they have the same timestamp. When I do the same in code, the timestamps are different:

val insertSql = "insert into table(id, db_timestamp) values(@id, SYSDATETIME())"
connection
     .createStatement(insertSql)
     .bind("@id", 1).add()
     .bind("@id", 2).add()
     .execute()

Is it possible that the code above creates two separate statements, instead of combining them into a single insert with multiples values?

Any idea how to achieve this?

[mp911de]

Apologies for the long radio silence. The past two years have been hefty and I hadn't had the mental nor time bandwidth for this project, now trying to catch up.

insert into table(id, db_timestamp) values (1, SYSDATETIME()), (2,SYSDATETIME());

is different from running two times the parametrized statement insert into table(id, db_timestamp) values(@id, SYSDATETIME()).

Both, JDBC and R2DBC run the statement using prepared statements (via Sp_CursorPrepExec or Sp_ExecuteSql depending on whether the fetch size is set) resulting in different times.

From the SQL server docs:

SQL Server obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

I assume that functions of the statement statement insert into table(id, db_timestamp) values (1, SYSDATETIME()), (2,SYSDATETIME()); get evaluated immediately to capture their values and then the statement is being executed resulting in fetching the same value twice, however, there's a chance that the two calls to SYSDATETIME return different values if the precision threshold is exceeded.

Let me know whether this helps.