I have 2 identical databases, db1 which is edited with updates and inserts
and db2 which is read-only.
Each table has an identifier column and a date-time-edited column.

Can anyone provide the basis of an sql script which would edit a table in
db2 to insert new rows from db1,

and update rows in db2 if they have changed in db1?

I don't want to use replication.

Regards Soc.

this is what I would do, syntax may be off somewaht as i did not create
a table to test on, but should be a fairly good guide for you.
Depending on your table size you may want to loop the code...

by setting a batchsize....and doing it for say

"WHERE keyCol between i and i+batchsize"
i = i + batchsize
and then loop this....


-- insert new values into 2nd Table
INSERT INTO db2.dbo.tableName (keyCol, col1,col2,col3)
SELECT
a.keyCol, a.col1, a.col2, a.col3
FROM db1.dbo.tableName a
LEFT OUTER JOIN db2.dbo.tableName b ON (a.keyCol=b.keyCol)
WHERE b.keyCol IS NULL

-- update table whereever values are different.

UPDATE  db2.dbo.tableName
SET
keyCol = a.keyCol
, col1 = a.col1
, col2 = a.col2
, col3 = a.col3
FROM db1.dbo.tableName a
INNER JOIN db2.dbo.tableName b ON (a.keyCol=b.keyCol)
a.col1 <> b.col1
OR
a.col2 <> b.col2
OR
a.col3 <> b.col3.







soc wrote:
Show quoteHide quote

soc
create trigger tru_MyTable on MyTable after update
as
if @@ROWCOUNT = 0
    return
insert MyAuditTable
select   i.ID,  d.MyColumn, i.MyColumn from   inserted  i join deleted  d
on  d.ID    = o.Id
go





Show quoteHide quote

Belated thank you for this help. Soc.

Show quoteHide quote

stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..
Iterating through SQL Server data
Require One of 3 Columns NOT NULL