Compare and Update

[Guest]

Hello,

We have Windows 2003 server wite SQL 2000 installed.
We like to compare Access Table and SQL 2k table and update SQL 2k table.

i.e.

inventory item master database

if SQL has same item then just update on hand quantity.
if SQL cannot find this item then just add this item to SQL.

Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Run 2 queries (these assume you have linked the SQL table):

UPDATE SQLTable As S INNER JOIN AccessTable As A
ON S.ItemID = A.ItemID
SET S.QtyOnHand = A.QtyOnHand
WHERE S.QtyOnHand <> A.QtyOnHand


INSERT INTO SQLTable (<column list>)
SELECT <column list>
FROM AccessTable As A LEFT JOIN SQLTable As S
ON A.ItemID = S.ItemID
WHERE S.ItemID IS NULL

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/FrpYechKqOuFEgEQLBlgCgk/WA5IaqiTM1Ro9axrBC5HjYg9IAoLGX
xVWA+Fh5yw+r9eAxwKgo0sIy
=E9ml
-----END PGP SIGNATURE-----