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.

#SQL #Ensemble
10
3 0 13 172
Log in or sign up to continue
Ken Earl · Jun 17

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.

00
Jimmy Christian · Jun 17

Thanks Ken.

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

00
Eduard Lebedyuk · Jun 17

In that case:

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

UNION

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

and so on.

00
Jimmy Christian · Jun 17

Thanks Eduard.

I get an error 

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

00
Robert Cemper · Jun 17

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'
10
Accepted answer
Jimmy Christian · Jun 18

Awesome !

Thanks Robert it worked without issues. Thank you.

00
Sebastian Thiele · Jun 18

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

00
Accepted answer
Jimmy Christian · Jun 18

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.

00
Julian Matthews · Jun 18

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

00
Accepted answer
Evgeny Shvarov · Jun 18

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
00
Accepted answer
Jimmy Christian · Jun 18

Thanks Julian. Yes that works as well. 

00