INSERT INTO with SELECT

André Vieira

Staff

Question

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é

26 Sep 2007

Barbosa

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

27 Sep 2007

2 replies

Last reply 27 Sep 2007

Show thread

Hide thread

André Vieira

Staff

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é

27 Sep 2007

Barbosa

nice post!

27 Sep 2007

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines