Question
Jimmy Christian · Jun 16

Bulk inserts using SQL in 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.

10
3 0 13 172
Log in or sign up to continue

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.

Thanks Ken.

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

In that case:

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

UNION

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

and so on.

Thanks Eduard.

I get an error 

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

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'

Awesome !

Thanks Robert it worked without issues. Thank you.

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

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.

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 :)

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

Thanks Julian. Yes that works as well.