New post
Discussion (10)0
Log in or sign up to continue
Robert Cemper · May 11, 2020

This is just a wrong format.
the example you pointed to simulates an INSERT..... SELECT...   construct 
with  no VALUES keyword !!

see docs: Insert Query Results: INSERT with SELECT

the SELECT combines single values with a UNION ALL clause.
All standard SQL.

1 0
Daniel Buxton · May 11, 2020

Hey Rob, 

Thanks for replying.  Yes I have had a look at that but it looks to me that the example copies existing data from another table.  I just want to add a bunch of new rows with one manual command.  I am afraid I am not an SQL guy so are you able to help reformat the below?

INSERT INTO ccms.NIActivityCode (ID, CustID, ActivityCode, Name, ShortName, CreateDate, Type, Server)
SELECT '1||1||102', '1', '102', 'Test1', 'test1', '2020-01-01 10:37:00', '4', '1'
UNION ALL
SELECT '1||1||103', '1', '103', 'Test2', 'test2', '2020-01-01 10:37:00', '4', '1'
UNION ALL
SELECT '1||1||104', '1', '104', 'Test3', 'test3', '2020-01-01 10:37:00', '4', '1'
UNION ALL

As far as I can tell the 'standard' SQL is formated as per below but that also didn't work

INSERT INTO ccms.NIActivityCode (ID,CustID,ActivityCode,Name,ShortName,CreateDate,Type,Server)
VALUES ('1||1||102','1','102','Test1','test1','2020-01-01 10:37:00','4','1'),
('1||1||103','1','103','Test2','test2','2020-01-01 10:37:00','4','1'),
('1||1||104','1','104','Test3','test3','2020-01-01 10:37:00','4','1');

0 0
Robert Cemper · May 11, 2020

INSERT INTO ccms.NIActivityCode (ID, CustID, ActivityCode, Name, ShortName, CreateDate, Type, Server)
SELECT '1||1||102', '1', '102', 'Test1', 'test1', '2020-01-01 10:37:00', '4', '1'
UNION ALL
SELECT '1||1||103', '1', '103', 'Test2', 'test2', '2020-01-01 10:37:00', '4', '1'
UNION ALL
SELECT '1||1||104', '1', '104', 'Test3', 'test3', '2020-01-01 10:37:00', '4', '1'
UNION ALL

UNION ALL adds to the previous Select.

ALL means all columns

1 0
Benjamin De Boe · May 11, 2020

Depends on how you're set up. There is a setting to allow specifying those values that is meant for this type of bulk load scenario.

@Daniel Buxton : the multi-row insert syntax you're asking about is currently not supported by IRIS SQL (as you figured by now :-)). Robert's approach using UNION all is a creative workaround, but depending on where your big INSERT is coming from you might also just be able to generate / rewrite it into separate INSERTs? Once you have that in place, you can use the SQL Shell's RUN command or the ObjectScript $SYSTEM.SQL.DDLImport() method to execute your script file.

0 0
Daniel Buxton · May 11, 2020

Hey Robert, 

Thanks very much for this.  It looks like I am closer but getting 

[SQLCODE: <-131>:<After Insert trigger failed>]

  [%msg: <21003,Cannot INSERT "NIActivityCode" because number is already used.>]

@Benjamin De Boe - thanks for letting me know its not available, I just figured I had a comma or apostrophe in the wrong place so I would have kept going around in circles.  It does let me INSERT a single row including the ID so yeah perhaps I will just have to do one at a time if I cannot get Roberts solution going

0 0
Robert Cemper · May 11, 2020

[SQLCODE: <-131>:<After Insert trigger failed>]

  [%msg: <21003,Cannot INSERT "NIActivityCode" because number is already used.>]

this simply means that you try to insert something that is unique and already present.
 so check against your table definition for uniqueness and next the data you want to insert.

Start with ID first.

It is definitely a data issue and not related to syntax

1 0
Robert Cemper · May 11, 2020

You might have a UNIQUE key on some of the other columns

I think your data is crap. (in the screenshot)
The ID you insert is totally different by structure than what you display from, table !!!

This one looks like a 3 level subscript (Server | CustID | ActivityCode)
I'd suggest not to try to insert ID at all. it is obviously generated

1 0
Evgeny Shvarov · Jun 9, 2020

Hi Daniel!

Another option is to have several SQL commands in a file, e.g. script.sql and run it one by one with:

do $System.SQL.DDLImport("IRIS",$Username,"/path/script.sql")

Learn more in documentation.

0 0