Question
Jimmy Christian · Jun 16, 2021

Bulk inserts using SQL in Ensemble

#SQL #Ensemble

Hello Community,

I am trying to insert multiple values in a table. Below is the simple sql statement.

Insert Into TableX

values ('Name', 'Address', 'Phone')

How can i do multiple inserts(rows) in one single statement?

Values are not in another table, so i cannot use Select into.

Thanks,

Jimmy Christian.

1
0 386
Discussion (14)4
Log in or sign up to continue
Ken Earl · Jun 17, 2021

This is how I would do it.  Use the following query to insert the data (note the colons before the variable names);

&sql(
        insert into tableX
            (firstname,middleName,surname)
        values
            (:firstname,:middleName,:surname)
    )

I don't know how you have your data stored, but assuming that the data is on an array, try something along these lines:

set idx=""
for {
    set idx=$order(personArray(idx))
    quit:idx=""

    set firstname=personArray(idx,"firstName")
    set middleName=personArray(idx,"middleName")
    set surname=personArray(idx,"surname")
     
    &sql(
        insert into tableX
            (firstname,middleName,surname)
        values
            (:firstname,:middleName,:surname)
    )
}

Hope this is useful.

0 0
Jimmy Christian · Jun 17, 2021

Thanks Ken.

Actually i am trying to insert in the SQL utility provided in Ensemble. Not using a script.

0 0
Eduard Lebedyuk · Jun 17, 2021

In that case:

Insert Into TableX
values ('Name', 'Address', 'Phone')

UNION

Insert Into TableX
values ('Name2', 'Address2', 'Phone2')

and so on.

0 0
Jimmy Christian · Jun 17, 2021

Thanks Eduard.

I get an error 

Input (UNION) encountered after the end of query^ INSERT INTO....

0 0
Robert Cemper · Jun 17, 2021

The SQL statement should look slightly different

Insert Into TableX (Name, Address, Phone)
SELECT 'Name1', 'Address1', 'Phone1'
UNION
SELECT 'Name2', 'Address2', 'Phone2'
UNION
SELECT 'Name3', 'Address3', 'Phone3'
1 0
Jimmy Christian · Jun 18, 2021

Awesome !

Thanks Robert it worked without issues. Thank you.

0 0
Alexander Koblov · Dec 22, 2021

I would even advise to use UNION ALL, because with UNION IRIS checks if resulting set has no duplicate rows, and there is no need to do this check with INSERT.

1 0
Sebastian Thiele · Jun 18, 2021

Hi,

you could also prepare the sql´s within an editor and in the SQL browser sqlect a dialect "MSSQL". The dialect field to choose from is only visible if you click the "more" button next to the max entries to return field.

Adter that you just place your inserts and you are good to go.

best regards,
sebastian

0 0
Jimmy Christian · Jun 18, 2021

That worked like a charm ! Thank you Sebastian.

Only thing is even if you insert more then 1 rows, it still tell you 1 Row (s) affected. Which is fine. But i had to just go and confirm that it worked.

0 0
Julian Matthews · Jun 18, 2021

Just to add an alternative to what has already been offered: you could also use the Data Import Wizard and provide the data in a CSV. You could then insert it into your table quite easily and without trying to built up a 100 union query :)

0 0
Evgeny Shvarov · Jun 18, 2021

Speaking of CSV import options here are two more:

  • CSVGEN - CSV import util to import from code or terminal
  • CSVGEN-UI - the web UI for CSVGEN to drag-n-drop
0 0