Inserting Multiple Events into the PI picomp2 Table: Navigating SQL and Data Type Challenges

In process data historian systems like OSIsoft PI, there are times when you want to insert multiple data points (or "events") in one go—whether for backfilling data, testing, or fixing data gaps. It seems straightforward, especially for those familiar with SQL: why not just use a bulk INSERT statement with multiple values or a union of SELECT statements? However, working with the PI OLEDB Provider and targeting the picomp2 table in the PI Archive isn't always that simple. Let's walk through the challenges and solutions often encountered in this scenario.

---

The Core Problem: Data Type Inference in UNION ALL

When you try to bulk insert data using the PI OLEDB provider, a common approach is to use an SQL UNION ALL statement:

INSERT INTO piarchive..picomp2 (tag, time, value, status)
SELECT 'sinusoid', '2024-06-01 09:00:00', 0, 0
UNION ALL
SELECT 'sinusoid', '2024-06-01 10:00:00', 0.8414709848, 0
-- ... more rows ...

However, users frequently encounter the following error:

OleDbException: Partial queries must have the same number of SELECT items and all corresponding SELECT items must be convertible to a data type of the item in the first partial query.

Why does this occur? The PI value column is a Variant type—flexible enough to accept integers, floats, strings, etc., but the SQL engine behind the PI OLEDB Provider infers the data type of each column from the first SELECT statement. If the first value is an integer, all subsequent values must also be integers, or convertible without loss to integer. Mixing integers and floats without explicit casting leads to errors.

Solution: Consistency & Casting

To resolve these errors:

• Ensure all corresponding columns in each UNION ALL SELECT have values of the same data type.
• Use explicit casting if you intend to use floats, e.g.,

INSERT INTO piarchive..picomp2 (tag, time, value, status)
SELECT 'sinusoid', '2024-06-01 09:00:00', CAST(0 AS float64), 0
UNION ALL
SELECT 'sinusoid', '2024-06-01 10:00:00', 0.8414709848, 0
UNION ALL
SELECT 'sinusoid', '2024-06-01 11:00:00', 0.9092974268, 0

Here, by casting the first value to float64, the SQL engine expects all subsequent values as floats.

---

Alternative Method: INSERT INTO ... VALUES (Linked Server)

Another approach mentioned is using the PI OLEDB Provider as a linked server from SQL Server. This allows you to use the more familiar bulk insert syntax:

INSERT INTO [MY-PI-SERVER].[piarchive]..[picomp2] (tag, time, value, status)
VALUES ('sinusoid', '2024-06-01 09:00:00', 0.8414709848, 0),
       ('sinusoid', '2024-06-01 10:00:00', 0.9092974268, 0)
-- etc.

However, be cautious about which table you are inserting into. The historical picomp table is generally read-only; data inserts should target picomp2 instead. Attempting to insert into picomp will not work and may generate permission or read-only errors.

---

Best Practices Recap

• Always use consistent data types across your UNION ALL clauses. If in doubt, use CAST(... AS float64) for floating point values.
• Don't mix integers and floats in your values within the same bulk insert—this triggers data type errors.
• Target the correct table: Use picomp2 for writing, not picomp.
• Test your queries on a small batch before running large imports, especially if you're restoring or replaying historical data.
• Consider bulk data loading tools: For larger jobs, programmatic insertion with AFSDK or PI Web API might be more robust than OLEDB SQL.

---

Conclusion

Bulk inserting events into PI's archive isn't quite the same as traditional SQL databases. Understanding how the PI OLEDB Provider handles data types is crucial for successful bulk inserts using SQL. With a little caution and the right approach to data typing, you can efficiently backfill or load PI data in bulk.

Have you run into similar issues or have lessons learned with PI data loading? Let us know in the comments!