Re: Mirror one table from 1 db to another

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 just added an extra field for comparison purposes. ... 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: Bit Datatype - Solved
    ... I suspect I know what I did. ... I have another table called Login with a key LoginID - no nulls ... The only way I can re-create the error is doing a stupid relationship to ... MS SQL Server 2005? ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Standard Programming Languages
    ... proficient in SQL and C, I suspect the goal of all of these products ... I suspect that the average analyst/investor is not familiar ... SQL or C. IMO, if they do not achieve the previously mentioned goal, ...
    (misc.invest.stocks)
  • Re: DATABSE IS SUSPECT BECAUSE OF MISSING FILES
    ... situation including resetting the suspect status. ... Andrew J. Kelly SQL MVP ... > missing files that SQL needs in order to remove the DB ...
    (microsoft.public.sqlserver.connect)