Re: Mirror one table from 1 db to another

Forget it, I just added an extra field for comparison purposes.
Works great.

Thanks a lot,
Andy.


"Andy DF" <nospam@xxxxxxxxxx> ha scritto nel messaggio
news:4497c2bf$0$973$5fc30a8@xxxxxxxxxxxxxxxxxx
Stephen,

Your INSERT on linked table is perfect!
I have linked DB A to DB B and works great.

I don't realy have any PK fields to compare though. Actually I have
Counter fields but since the values are assigned automatically and
indipendently for each DB, that makes them useless.

I need to insert into MyTable of DB A all records from MyTable in DB B
THAT WHERE NOT PREVIOUSLY INSERTED.
Records consist only of 2 fields: a Numeric field and a DateTime Field.

Thanks a lot for your help.
Andy.

"Stephen Howe" <sjhoweATdialDOTpipexDOTcom> ha scritto nel messaggio
news:OH8dwYAlGHA.5036@xxxxxxxxxxxxxxxxxxxxxxx
I need to be able to merge data regularly from a certain table on DB B
to the corresponding table in DB A.

How so?
Is table on DB A supposed to be identical to the table on DB B?
I suspect the answer is no.

The way I would do this is 2 steps

(i) make DB A aware of DB B. You can do this by adding a linked server.
All databases support this AFAIK.
(ii) Having done this, issue some SQL to merge regularly. I suspect you
want do SQL INSERTs and possibly SQL UPDATEs but not SQL DELETEs

So

INSERT INTO table1 SELECT * from linkeddb.dbo.table1 lt1 LEFT JOIN table1
t1 ON lt1.field1=t1.field1
WHERE t1.field1 IS NULL

should do it. Your inserting rows present on DB B that are not present on
DB A.
The ON clause should contain a full list of PK fields.
A Connection Execute should do it, no need to bother with Recordsets.

The UPDATE is similar if you wish to update non PK-fields on DB A which
are now different on DB B.

UPDATE table1 SET nonpkfield1=lt1.nonpkfield1,
nonpkfield2=lt1.nonpkfield2
FROM linkeddb.dbo.table1 lt1 JOIN table1 t1 ON lt1.field1=t1.field1
WHERE t1.nonpkfield1<>lt1.nonpkfield1 OR t1.nonpkfield2<>lt1.nonpkfield2

The WHERE clause filters out all the rows where all nonPKfields are
identical.

All of this should be a lot faster as you are making the Jet Engine work
for you.

Stephen Howe





.

Relevant Pages

  • Re: Mirror one table from 1 db to another
    ... I suspect the answer is no. ... issue some SQL to merge regularly. ... Your inserting rows present on DB B that are not present on ... The WHERE clause filters out all the rows where all nonPKfields are ...
    (microsoft.public.data.ado)
  • Re: Mirror one table from 1 db to another
    ... I suspect the answer is no. ... issue some SQL to merge regularly. ... Your inserting rows present on DB B that are not present on DB ... The WHERE clause filters out all the rows where all nonPKfields are ...
    (microsoft.public.data.ado)