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.
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.
How bulky are we talking about?
Around 100 rows.
In that case:
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
Awesome !
Thanks Robert it worked without issues. Thank you.
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.
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:
Thanks Julian. Yes that works as well.
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