# \[IGN-7967\]Does reWriteBatchedInserts=true work on MSSQL? **URL:** \ **Category:** Ignition\ **Tags:** perspective, database\ **Created:** [December 10, 2025, 8:33pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747 "2025-12-10T20:33:30Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 10, 2025, 8:33pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/1 "2025-12-10T20:33:30Z")
We have an Ignition 8.2 project in the POC stage logging to a MSSQL 2019 datasource. It has roughly 200 tags set up for logging at 10 second intervals (no deadbanding) so we’re dumping 20 records/sec to the database. I added reWriteBatchedInserts=true to the Extra Connection Properties box and then disabled and re-enabled the driver, but I’m still seeing the records go into the database one at a time with SQL profiler. Any suggestions? ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb39a8aa2165e19a5fbb403032b5a18ca59f512c.png) ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [December 10, 2025, 8:38pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/2 "2025-12-10T20:38:01Z")
I can't find any evidence or documentation that that MSSQL JDBC driver supports this parameter. ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 11, 2025, 1:26am UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/3 "2025-12-11T01:26:35Z")
Thanks so much for your quick reply. Can you tell me a little more about this? I know reWriteBatchInserts is more a PostgreSQL thing, but ..well.. batched inserts have been in dusty HMIs like Cimplicity PE for maybe 20 years now. Is it a super heavy lift to add this feature? It _would_ have a significant positive performance impact on heavily loaded SQL hosts since you’re not having to spin up and close out a transaction for every single record. ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 11, 2025, 1:34am UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/4 "2025-12-11T01:34:33Z")
Or maybe bulk inserts on MSSQL JDBC are enabled some other way? It looks like there is rudimentary support in the driver for it. > **[How to Perform a Bulk Insertion using JDBC? - GeeksforGeeks](https://www.geeksforgeeks.org/java/how-to-perform-a-bulk-insertion-using-jdbc/)** > > Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and... ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 11, 2025, 1:44am UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/5 "2025-12-11T01:44:54Z")
Ignition wraps JDBC drivers in a vanilla JDBC connection pooling wrapper. The actual vendor-specific objects are **not** reachable without extensive reflection and behavioral work-arounds. Good luck. As it happens, you can pass arrays into the vanilla wrapper to be handed to PostgreSQL's `UNNEST()` function. Doesn't seem to work with other brands. > [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4): > > If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase… > [@PostgreSQL array support](https://forum.inductiveautomation.com/t/postgresql-array-support/79507/6): > > UNNEST is in the SQL standard. Oracle has equivalent functionality with different syntax (the predefined collections tools, from what I understand). This functionality is also present (as UNNEST) in Google's cloud data warehouse. Presumably others. Microsoft simply lags other platforms, and has for many years. My bias towards DB-agnostic solutions is to avoid vendor lock-in. My bias towards PostgreSQL is due to their stated policy of hewing close to the standard wherever possible. This me… ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [December 11, 2025, 1:52am UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/6 "2025-12-11T01:52:03Z")
Try `useBulkCopyForBatchInsert=true` > **[Bulk copy API for batch insert in JDBC - JDBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver17)** > > Microsoft JDBC Driver for SQL Server supports using Bulk Copy for batch inserts for faster loading of data into the database. Not sure if this works unless the caller is explicitly creating batches, though. ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 11, 2025, 3:36pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/7 "2025-12-11T15:36:11Z")
I added the useBulkCopyForBatchInsert=true in the Extra Connection Properties box. It didn’t make any difference in the Profiler traces that I can tell. How would one explicitly create batch inserts with a collection of 200 tags being historized every 10 seconds? ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [December 11, 2025, 3:38pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/8 "2025-12-11T15:38:30Z")
> [@joe.manns](#): > > How would one explicitly create batch inserts with a collection of 200 tags being historized every 10 seconds? If you're using tag history you wouldn't, this is something the history system would do. I could have sworn it already did that... and I'm having some weird deja vu from an old thread about this where the profiler just doesn't make it clear that a batch is happening, but I could be completely misremembering. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [December 11, 2025, 3:43pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/9 "2025-12-11T15:43:49Z")
Apparently this is a known issue with MSSQL and the historian specifically. We do attempt to use batched queries on the backend, but (according to the ticket) the MSSQL JDBC driver rejects them because we use `datetime` instead of `datetime2` as the column type. Changing your translator settings _might_ fix this particular issue (on _future_ historian data tables we automatically create), though might also cause any number of other issues. ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 11, 2025, 3:49pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/10 "2025-12-11T15:49:02Z")
Ignition’s tstamp column in sqlth\_1\_data is a bigint, not a datetime or datetime2. Where are the datetime & datetime2 columns being defined or referenced? ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [December 11, 2025, 6:59pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/11 "2025-12-11T18:59:38Z")
Not the `t_stamp`, the `datevalue` column. Apparently even though it's null in the inserts, that's enough to break the optimization in the driver. Again, according to the ticket 🤷 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 11, 2025, 7:16pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/12 "2025-12-11T19:16:23Z")
It has been a really long time since MS introduced the ~~`datetime2`~~ `datetimeoffset` column type, that doesn't lose the true point-in-time, necessary to properly restore the correct `java.util.Date` in all connection timezones. This default in the translator should be changed. (And for PostgreSQL, changed from `timestamp` to `timestamptz`.) Ideally, the MariaDB translator would be changed to `timestamp(3)` too, but docs would need to strongly suggest the use of the `explicit_defaults_for_timestamp` setting, to avoid the unfortunate automatic default behavior. (That the setting exists is an admission that the original behavior was ridiculous.) ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 12, 2025, 7:57pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/13 "2025-12-12T19:57:59Z")
Thanks Phil for the pointers. I tried changing the `datetime` column in the translator to `datetime2`, disabled the driver then re-enabled. No apparent change (i.e. no batched inserts) shown in the profiler. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 12, 2025, 8:02pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/14 "2025-12-12T20:02:26Z")
The translator value would only take effect when creating new tables. For existing tables, you will need to alter the columns yourself. Depending on the connection time zone, you may need to: - add a new column of ~~`datetime2`~~ , - populate it with values from the original column, applying the right time zone, - alter the original column to ~~`datetime2`~~ , - copy from the new column to the original, - then drop the temporary column. { Be sure to disable the historian connection during steps 3 & 4. } ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 12, 2025, 8:13pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/15 "2025-12-12T20:13:40Z")
`datetime2`column types in MSSQL aren’t timezone aware - for that you use `datetimeoffset`. Actually sqlth\_1\_data in our POC database is a view with instead-of triggers, not a table. I can’t imagine this is complicating things 😀 FWIW I did change the column type in the view to a `datetime2`. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 12, 2025, 8:16pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/16 "2025-12-12T20:16:46Z")
Hmm. Yes, `datetimeoffset` is the correct type. I think I used to know that, but don't use MS SQL enough. (Not unless a client firmly demands it.....) ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 12, 2025, 8:27pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/17 "2025-12-12T20:27:52Z")
I tried changing the `datetime` column in the translator to `datetimeoffset`, changed the view return type to `datetimeoffset` as well, disabled the driver then re-enabled. No apparent change (i.e. no batched inserts) shown in the profiler. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [December 12, 2025, 8:29pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/18 "2025-12-12T20:29:20Z")
If you haven't altered the existing **tables** yourself, then they are still `datetime`, and you won't see a difference for inserts to those. If you've redefined `sqlt_data_....` to be a view, and are relying on insert triggers to actually move the data to real tables, then **all bets are off**. Per this, any bulk copy can only target actual tables, not views: > **[Using bulk copy with the JDBC driver - JDBC Driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/jdbc/using-bulk-copy-with-the-jdbc-driver?view=sql-server-ver15)** > > The SQLServerBulkCopy class allows you to write data load solutions in Java that offer significant performance advantages over the standard JDBC APIs. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [December 12, 2025, 8:34pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/19 "2025-12-12T20:34:43Z")
Also, if this note applies to SQL Server and not just "Azure SQL DW", which I suspect it does: [Bulk copy API for batch insert in JDBC - JDBC Driver for SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/connect/jdbc/use-bulk-copy-api-batch-insert-operation?view=sql-server-ver15#known-limitations:~:text=Because%20of%20the%20limitations%20of%20Bulk%20Copy%20API%2C%20MONEY%2C%20SMALLMONEY%2C%20DATE%2C%20DATETIME%2C%20DATETIMEOFFSET%2C%20SMALLDATETIME%2C%20TIME%2C%20GEOMETRY%2C%20and%20GEOGRAPHY%20data%20types%2C%20are%20currently%20not%20supported%20for%20this%20feature%20against%20Azure%20SQL%20DW). Then `datetime2` is the only one that has a chance? ---
**Author:** ![joe.manns](https://avatars.discourse-cdn.com/v4/letter/j/8c91f0/32.png) [@joe.manns](https://forum.inductiveautomation.com/u/joe.manns)\ **Post date:** [December 12, 2025, 8:40pm UTC](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747/20 "2025-12-12T20:40:23Z")
That article does say something about logging an error before going to the fallback (which I assume is happening) Is this error log available somewhere? I’m not seeing anything in the Diagnostics→Logs dashboard. FWIW, we’re running an 8.3 project, not an 8.2 [Next page](https://forum.inductiveautomation.com/t/ign-7967-does-rewritebatchedinserts-true-work-on-mssql/112747.md?page=2)