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

[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]

What should happen, if there's already content in the target field?

[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

[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]

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]

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.

[ic2]

Hello Karl,

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]

DIck,
looks like this is something in ADS - just tried it with PG and it works flawless.

[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

[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