How to insert a row in the database

How to insert a row in the database


I have created a dummy entity, Entity1 with a field ID (autonumber) and a field Texto of type Text.
I want to use a Advanced Query to insert into the DB.
I have tried to use the following code:

INSERT INTO {Entity1} VALUES("example")

i received an error in the word VALUES. ( Unexpected 'VALUES')


I've just resolved the problem with

INSERT INTO {Entity1} (a)
VALUES ( 'Example')

I'm not sure if you already know this - you may also use the following Entity Actions that are automatically created for you, when you defined the Entity1 entity:
  • CreateEntity1
  • CreateOrUpdateEntity1
To use these actions, follow these steps:

1. Define a new record variable to hold the values. In the eSpace Tree, right click over the action you are editing, and select [Add Local Variable]. Set the following property values:
Name: MyRec
Data Type: Record
Record Definition: Entity1

2. From the Tools Tree, drag an Assign element to the canvas. Set the following property values:
Variable 1: MyRec.Entity1.Texto
Expression 1: "Example"

3. In the Tools Tree, expand Entity Actions, expand Entity1, and drag CreateEntity1 to the canvas, just after the Assign. Set the following property values:
Source: MyRec

I would advise you to use CreateEntity1 instead of an Advanced Query. Among other advantages, you'll be able to get the primary key's value of the inserted row (through the ID output parameter of the CreateEntity1 execute action), you'll also be able to rename the Entity without having to manually edit the advanced query text, and it will be crossed platform compatible (sql server, oracle).


I´ve tried to Insert values using advanced query but table always stay empty, with a dummy entity like Hugo Jesus used. Is it because you cannot update tables directly in service studio. Is it the exposure read only that prevents me from modifying the table in Service Studio?

The only way to modify values is using the web browser after launching the application?

I´ve tried:
INSERT INTO {Ent1} (At1) 
VALUES ( 'Example') 

Result was:

"Query returned no rows"

Thank you
Hi Pedro,

The fact that the the query returns no rows, doesn't mean that the data hasn't been inserted - after all, if I recall correctly, the Insert command in SQL doesn't return any rows. However, are you executing that command, for an entity whose definition is the same as Hugo's - i.e. an autonumber ID, and a Text field?

I don't recall there being any restriction on updating the database through advanced SQL - even though I am curious as to why you are not using the entity actions that Service Studio automatically creates for you.

Also, what you have to bear in mind is that the transaction unit is the full web request, so you can manually abort a DB transaction by code after the insertions and editions. By the same criteria, I am not sure if in the case of an exception, the transaction is aborted or not. What you can try to do is execute a CommitTransaction action (under the Built-in Actions folder) and see if it works for you.

Let me know how you approach this, and if you have solved this problem.

If not, let us know in more detail what you're experiencing.


Paulo Tavares

are you doing that insert in a runtime context or in the test query without having results? If it is in test query, the transaction isn't committed so you'll never have values until you commit that transaction.

Keeping focus on best practices...the reason why you are doing an insert without using the entity action is...?


P.S - If the entity you are trying to create one record is a referenced entity and has the read only turn to yes, you won't be able to insert values unless it is made in the espace entity's owner
Hello again

First of all thank you for your quick responses Paulo and Ricardo. "If it is in test query, the transaction isn't committed so you'll never have values until you commit that transaction." Yes that was what i tried to do. So a commit was missing.

But i wanted to know what do you recommend for good pratices. Only inserting elements on the database tables on runtime(not in service studio)?

To kill your curiosity ;) I was trying to test something and it was quicker then doing a webpage to insert elements, but also i wanted to test the flexibility of Outsystems.

P.S. The Commit Transaction entity action(Built in action) it is only to execute on runtime? Or can i execute the action directly in Service Studio, to commit new rows?

Thanks again Best Regards
Pedro Menezes
Hi Pedro.

Thanks for your reply. Indeed, if you are trying to insert data in the database, it should not be done in Service Studio, that is definitely not its purpose! :) After all, if you're looking to insert data in the tables, there are other solutions you could try:

- First of all, depending on the data you're trying to create, you could use Service Studio's 5.1 IntelliWarp and import the data directly from an Excel file ;
- Second, if it's supposed to be bootstrap data - i.e. data that should exist in the database when the application is installed - you could create a static entity, and edit the data directly in Service Studio, as in the following picture:

- Third, you could always create all the data in an Excel spreadsheet, and then create a screen that would load the excel file and create the data;
- And last, if you're going to write SQL scripts to do so, you could always use SQL Server Management Studio - I think it also has a Grid-like editor that you can write directly into ;), if I'm not missing anything, those are the recommended options for pre-loading data.

Otherwise, yes, all data insertions that are not supposed to be pre-loaded should be done in runtime.

Also, any kind of logic you design in Service Studio can only be executed in runtime, yes. So you cannot use the CommitTransaction built-in action while IN Service Studio - but you could add a COMMIT statement to the Advanced Query, to do the trick.

Does this help?




The commit transaction action is something that you should use when you know you need that transaction committed, due by performance issues or the process that you design need to be sure that the actions you've made before that step are committed.

"Only inserting elements on the database tables on runtime(not in service studio)?" >>> SURE!!!! When creating a new record you may have business logic associated to it and if you do one explicit insert like you did you are ignoring that part of the code.

By the way, designing one dummy screen in 4.2 or 5 only takes you 15 minutes maximum (1CP included) 5.1 only takes 30 seconds to drag the entity to the webflow and right click to create the edit screen and 30 seconds to publish (1CP) :)

Don't make it right since the beginning 

If more help needed just ask, because we are here to help.



You`ve told me everything i needed to know about this subject, that was exactly what i wanted to know, very good replies.


Best Regards