INSERT INTO with SELECT

INSERT INTO with SELECT

  
Hi all,

If you ever think of using and Advanced Query in Service Studio with the pattern in subject then pay attention to this post.

The OutSystems Platform is a build to change environment meaning that it is easy for your to change things like business logic and data model. The platform is also kind enough to hide the necessary transformations so that we don't have to worry about them. Take the following example: I created an Entity (let's call it suspect A) with a couple of attributes:
A
-----
Id
Name
Email

Phone


After doing this I published my eSpace. What happens behind the scenes is that OutSystems Platform creates the necessary structures in the database to support this entity, namely tables, triggers, etc.
In the meantime I decided that my entity was lacking a couple of attributes and I also decided that the order of the attributes was not the one I wanted. So I added the attribute Country and discarded the attribute Email (no matter for what reason). My entity now was:
A
----
Id
Name
Country

Phone


Now I wanted to import data from another table that I had access and I though of using the pattern INSERT INTO with SELECT. I added an advanced query like the following one:
INSERT INTO A

SELECT null Id, B.Name Name, B.Country Country, B.Phone Phone FROM
B


(Can anyone guess what happen?)

What happened was that my table in the database was
A
----
Id
Name
Email
Phone

Country

and since I didn't provide the columns that should be filled by the query the database engine was trying to match
A.Id <-> null
A.Name <-> B.Name
A.Email <-> B.Country
A.Phone <-> B.Phone

So how do we fix this? Just state the columns to where you want to insert:
INSERT INTO A(Id,Name,Country,Phone)

SELECT null Id, B.Name Name, B.Country Country, B.Phone Phone FROM
B


If you ever used this (and it may be even working) remember that this is an accident waiting to happen. Go back to your queries and add the columns.

Hope this helps, Cheers,
André
Hello Andre


this query
"INSERT INTO A
SELECT null Id, B.Name Name, B.Country Country, B.Phone Phone FROM
B"

in 4.0.xxxx with sql 2005 default instalation will return an error, unless you use the column values and change the INDENTITY INSERT to ON

this query

"INSERT INTO A(Id,Name,Country,Phone)
SELECT null Id, B.Name Name, B.Country Country, B.Phone Phone FROM
B"

will also return an error stating that default or null values can not be used in an identity column.

you can however do this...

insert into {A} ({A}.[Name] ,{A}.[Email] ,{A}.[Phone] )
select {B}.[Name] ,{B}.[Email] ,{B}.[Phone]
from {B}

cheers
Hi Rui,

Thank you for your update.
I wasn't being specific about what database I was using, but in fact it was Oracle :)
Cheers,
André
nice post!