Page 1 of 2
Is it possible to merge info from 1 database into another using SQL
Posted: Fri Jul 12, 2024 4:31 pm
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
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Fri Jul 12, 2024 5:14 pm
by FFF
What should happen, if there's already content in the target field?
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Fri Jul 12, 2024 5:33 pm
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
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Mon Jul 15, 2024 12:42 pm
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
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Mon Jul 15, 2024 2:02 pm
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
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Mon Jul 15, 2024 2:43 pm
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.
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Mon Jul 15, 2024 7:28 pm
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
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Tue Jul 16, 2024 8:03 am
by FFF
DIck,
looks like this is something in ADS - just tried it with PG and it works flawless.
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Tue Jul 16, 2024 8:50 am
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
Re: Is it possible to merge info from 1 database into another using SQL
Posted: Tue Jul 16, 2024 9:53 am
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