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.
Comments
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.
.png)
How bulky are we talking about?
Around 100 rows.
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.
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.
.png)
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 theData Import Wizardand 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 :)