|
|
Rank: Newbie
Groups: Member
Joined: 8/26/2009
Posts: 4
Location: Boston
|
Is it possible to perform a multiple INSERT with the PI-OLEDB? For example, if I have the values:
25-Aug-09 00:00 9.3
25-Aug-09 00:10 9.4
25-Aug-09 00:20 8.9
Is there a syntax such that I may insert all three instead of calling INSERT three times? What I mean is, instead of:
INSERT piarchive..picomp2 (time, value, tag) values ('25-Aug-09 00:00', 9.3, 'SomeTag')
INSERT piarchive..picomp2 (time, value, tag) values ('25-Aug-09 00:00', 9.4, 'SomeTag')
INSERT piarchive..picomp2 (time, value, tag) values ('25-Aug-09 00:00', 8.9, 'SomeTag')
can I do:
INSERT piarchive..picomp2 (time, value, tag) (('25-Aug-09 00:00', 9.3, 'SomeTag'), ('25-Aug-09 00:00', 9.4, 'SomeTag'), ('25-Aug-09 00:00', 8.9, 'SomeTag'))
instead? I tried several variations of this syntax and none appeared to work. I am writing a custom application that processes PI data with MATLAB, and would like to do this with SQL if at all possible (i.e. a PI-SDK solution is not going to be viable). Thanks in advance.
|
|
|
|
|
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 409
Location: Cheshire, United Kingdom.
|
Hi Michael, Not tested this and not got time right now but feel free to test the theory. In SQL Server you can use UNION ALL to build multiple inserts. So in this case you could try: Code:
INSERT piarchive..picomp2 (time, value, tag)
SELECT ('25-Aug-09 00:00', 9.3, 'SomeTag')
UNION ALL
SELECT ('25-Aug-09 00:00', 9.4, 'SomeTag')
UNION ALL
SELECT ('25-Aug-09 00:00', 8.9, 'SomeTag')
UNION ALL
I will take a look at this myself out of my own curiosity shortly... Cheers, Rhys.  OSIsoft PI System SpecialistsPI consultancy on PI Systems, PISDK, AFSDK, OLEDB etc and PI custom developments. Well pretty much anything to do with PI!
|
|
|
Rank: Newbie
Groups: Member
Joined: 8/26/2009
Posts: 4
Location: Boston
|
Thanks Rhys. I attempted the following query: Code:INSERT piarchive..picomp2 (time, value, tag)
SELECT ('25-Aug-09 00:00', 9.3, 'Tracktag')
UNION ALL
SELECT ('25-Aug-09 00:10', 9.4, 'Tracktag')
UNION ALL
SELECT ('25-Aug-09 00:20', 8.9, 'Tracktag')
UNION ALL and recieved the following error message: Code:??? Invoke Error: Dispatch Exception:
Source: PIOLEDB
Description: [SQL Parser] [Line 1] Syntax error near ','. I am not sure which comma the error refers to, but when I attempt a similar query with only two entries (i.e. the first two data points) I recieve the same error message. Looking at the PIOLEDB documentation, the 'UNION' statement appears in the list of supported ANSI SQL-92 statements, but there are no examples of it in the 'Compendium of PI-SQL Statements'. If it matters, I am using PI-OLEDB version 3.2.2.10 on a HA Collective.
|
|
|
Rank: Administration
Groups: Administration
Joined: 6/20/2008
Posts: 409
Location: Cheshire, United Kingdom.
|
Hi Michael, Finally had some time to take a look, here you go :-) Code:
INSERT piarchive..picomp2 (time, value, tag)
SELECT '25-Aug-09 00:00', 9.3, 'sinusoid'
UNION
SELECT '25-Aug-09 00:10', 9.4, 'sinusoid'
UNION
SELECT '25-Aug-09 00:20', 8.9, 'sinusoid'
Enjoy! Rhys. OSIsoft PI System SpecialistsPI consultancy on PI Systems, PISDK, AFSDK, OLEDB etc and PI custom developments. Well pretty much anything to do with PI!
|
|
|
Rank: Newbie
Groups: Member
Joined: 8/26/2009
Posts: 4
Location: Boston
|
Thanks again Rhys. That query worked out.
A note to anyone who finds this in the future: I received a SQL parser stack overflow when I exceeded ~900 records. For data larger than this, either break it up into smaller chunks or write it out to a text file and let the Universal File Loader do the heavy lifting.
|
|
|
|
Guest
|
