Question
· 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.

Discussion (14)2
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