Is it possible to merge info from 1 database into another using SQL

This forum is meant for questions and discussions about the X# language and tools
ic2
Posts: 1851
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

Is it possible to merge info from 1 database into another using SQL

Post by ic2 »

From time to time I have to import some info in an existing database. I now consider to write a general program that imports database 1 (the export) into database 2 (an existing database in my program) into field(s) X,Y, Z using key A = Field B in one of our databases.

I wonder if there's a smart SQL statement which can do the same (I can apply that in ADS' Data Architect)?

Example:

Address database

Code: Select all

Clientkey    Name     X Y            Remark

1           Someone
2           Else
3           Another

Code: Select all

[b]Import database[/b]

Clientkey    Remark
2            A remark
3	     Another remark
After merging these 2, the address database should look like this:

Code: Select all

Clientkey Name     X Y                Remark

1            Someone
2            Else                     A remark
3            Another                   Another remark
Dick
FFF
Posts: 1562
Joined: Fri Sep 25, 2015 4:52 pm
Location: Germany

Re: Is it possible to merge info from 1 database into another using SQL

Post by FFF »

What should happen, if there's already content in the target field?
Regards
Karl
(on Win8.1/64, Xide32 2.20, X#2.20.0.3)
FFF
Posts: 1562
Joined: Fri Sep 25, 2015 4:52 pm
Location: Germany

Re: Is it possible to merge info from 1 database into another using SQL

Post by FFF »

In PG, this would be, i think:

Code: Select all

MERGE INTO addresses USING export ON export.clientkey = addresses.clientkey 
WHEN matched THEN UPDATE SET remark = export.remark
Regards
Karl
(on Win8.1/64, Xide32 2.20, X#2.20.0.3)
ic2
Posts: 1851
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

Re: Is it possible to merge info from 1 database into another using SQL

Post by ic2 »

Hello Karl,

Worked like a charm in ADS Data Architect!

That saves me quite some time (normally I write some temp DO while utility for that; not too difficult either but this was only changing some names and issuing the command).

Thanks a lot!

Dick
ic2
Posts: 1851
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

Re: Is it possible to merge info from 1 database into another using SQL

Post by ic2 »

I had one (different) merge to do where I wanted the field replaced only when it was empty. But that gives errors in ADS Data Architect. I tried several different construction looking at several websites but I would say I only had to add to your fully working statement

where addresses.remark IS NULL

I then get:

Code: Select all

MERGE INTO addresses USING export ON export.clientkey = addresses.clientkey  WHEN matched THEN UPDATE SET remark = export.remark where addresses.remark IS NULL

but ADS directly gives the following error and points to the 'w' of the added 'where:

Unexpected token: where -- Expecting semicolon. -

There are no semicolon's in DataArchitect so it doesn't like the 'WHERE'' at all , that is: in this construction, because this works without problems:

Code: Select all

update addresses set remark ='test' where addresses.remark IS NULL
What could be wrong in the above statement then?

Dick
FFF
Posts: 1562
Joined: Fri Sep 25, 2015 4:52 pm
Location: Germany

Re: Is it possible to merge info from 1 database into another using SQL

Post by FFF »

Dick,
i think:

Code: Select all

MERGE INTO addresses USING export ON export.clientkey = addresses.clientkey  WHEN matched AND addresses.remark IS NULL THEN UPDATE SET remark = export.remark
should do the trick.
Regards
Karl
(on Win8.1/64, Xide32 2.20, X#2.20.0.3)
ic2
Posts: 1851
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

Re: Is it possible to merge info from 1 database into another using SQL

Post by ic2 »

Hello Karl,
FFF wrote: Mon Jul 15, 2024 2:43 pm Dick,
i think:

Code: Select all

MERGE INTO addresses USING export ON export.clientkey = addresses.clientkey  WHEN matched AND addresses.remark IS NULL THEN UPDATE SET remark = export.remark
should do the trick.
I'm afraid not (I think it's one of the combi's I tried).

Error is Expected lexical element not found: THEN (which is there but the error of the 'missing' THEN points to where it says AND).
I tried a few variations with brackets but still no luck. Maybe it's an Advantage SQL issue only but as your previous statement worked directly you'd say such a simple extra condition shouldn't be too hard to insert somewhere....

Dick
FFF
Posts: 1562
Joined: Fri Sep 25, 2015 4:52 pm
Location: Germany

Re: Is it possible to merge info from 1 database into another using SQL

Post by FFF »

DIck,
looks like this is something in ADS - just tried it with PG and it works flawless.
Regards
Karl
(on Win8.1/64, Xide32 2.20, X#2.20.0.3)
User avatar
wriedmann
Posts: 3739
Joined: Mon Nov 02, 2015 5:07 pm
Location: Italy

Re: Is it possible to merge info from 1 database into another using SQL

Post by wriedmann »

Hi Dick, hi Karl,
one important thing: SQL is not SQL, and specially in such things that are not covered by an SQL standard there can be major differences between different SQL engines.
And ADS ist not a fully standard compliant SQL engine.
Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
wolfgang@riedmann.it
https://www.riedmann.it - https://docs.xsharp.it
ic2
Posts: 1851
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

Re: Is it possible to merge info from 1 database into another using SQL

Post by ic2 »

Thanks Karl, Wolfgang, I already thought something like that. I've dealt with a few smaller ADS SQL specific issues earlier (easier things than this query).

I can still reach my goal without programming. I can use the original statement to fill a field in the database which is empty everywhere. With my dbf utility I can then fill the empty fields only and also merge the 2 field contents for non empty fields.

Dick
Post Reply