Update SQL Table

[sjesweak]

Hi all -

I am wondering how I would go about updating data from one table to another entirely each night.

Here's what I have so far

CREATE TABLE #tempTable (KEY_ID nchar(10), BOL_NUM nchar(20), DATE_ENTER DATETIME, DATE_RCVD DATETIME, MSTR_VEND_ID nchar(10), PART_NUM nchar(10),
PO_NUM nchar(10), PUR_UOM nchar(10), QTY_RCVD integer, RCVR_NUM nchar(10), SHIP_DATE DATETIME, SHIP_NUM NCHAR(10))

INSERT INTO #tempTable
SELECT * FROM OPENQUERY(CMISERVER, 'SELECT KEY_ID, BOL_NUM, DATE_ENTER, DATE_RCVD, MSTR_VEND_ID, PART_NUM, PO_NUM, PUR_UOM, QTY_RCVD, RCVR_NUM, SHIP_DATE,
SHIP_NUM FROM V_RPS_RCPT_MK')

INSERT INTO T4M.dbo.tbl_RPS_RECEIPTS_MK
SELECT * FROM #tempTable WHERE KEY_ID NOT IN ( SELECT KEY_ID FROM tbl_RPS_RECEIPTS_MK)


RETURN


What I want to do is after the insert statement has found the extra fields that have been added to then do an update to make sure all data is correct in both sources. I'm synchronizing data from a unidata database and a sql database.

 

[rac2]

...all data is correct in both sources...

I think this is not going to be easy. Is there a utility function in the Unidata system for synchronizing or replicating data? SQL Server has some capabilities like this, I have never used them.

"correct" is probably too much to hope for. You might be able to get to "is the same".

Is the data changing in both databases? If so, how do you decide which one has the correct data?

Is there a timestamp in one of the tables which would tell you which rows have been updated since the last sync?