INSERT multiple rows
Hey I would like to INSERT mulitple rows to 1 table. I need to intersystems cache equivelant for
INSERT INTO table_name (column_list)
VALUES
(value_list_1),
(value_list_2),
(value_list_3);
Have tried this post https://community.intersystems.com/post/how-execute-multiple-query-or-script-sql-management-studio
but still got an error
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.
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');
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 ALLUNION ALL adds to the previous Select.
ALL means all columns
Also ID usually can't be inserted.
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.
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
[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
Data is defenitely unique :S
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
Hi Daniel!
Another option is to have several SQL commands in a file, e.g. script.sql and run it one by one with:
Learn more in documentation.
Social networks
InterSystems resources
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue
Log in or sign up
Log in or create a new account to continue