Insert or Update Row if Primary key isn't pre-existing in table SQL 2000

I'm trying to extract data from our Accounting Database and use it in
another database that's used for our web site. Last month I created a SQL
Select Query to give me the product information I need to put into our web
site database. I then used DTS to copy that data from one database to
another. Now I need to update my web site database with any new products
that have been added into our Accounting database without changing any
existing rows in the web site database. Basically, I just need to be able
to add a new row for a product if the product ID (primary key) isn't
anywhere in my web site database. Here's my original query to retrieve
product info from the Accounting database:
SELECT DISTINCT IV00101.ITEMNMBR AS ID, IV00101.ITEMDESC AS Title,
IV00101.USCATVLS_2 AS Category
FROM IV00101 LEFT JOIN AARG_Inv_UserDef_Item ON IV00101.ITEMNMBR =
AARG_Inv_UserDef_Item.ITEMNMBR
WHERE ((IV00101.ITEMTYPE)=1 AND ((IV00101.USCATVLS_2)<>'box' And
(IV00101.USCATVLS_2)<>'replicator'
And (IV00101.USCATVLS_2)<>'components' And (IV00101.USCATVLS_2)<>'displays'
And (IV00101.USCATVLS_2)<>'Dist Audio'
And (IV00101.USCATVLS_2)<>'Dist Video' And (IV00101.USCATVLS_2)<>'Dist
Games' And (IV00101.USCATVLS_2)<>'Dist Softw'
And (IV00101.USCATVLS_2)<>'Dist Books' ))
ORDER BY IV00101.ITEMNMBR;
RESULTS
40139 James Earl Jones reads the Bible (CD/Small)
Devotional
40151 In Their Own Words: Space Race (CD/Small) Spoken
Word
40155 Old West Collection (CD/Small)
Spoken Word
40159 Lewis & Clark Collection (CD/Small)
Spoken Word
40162 Ingles (CD/Large)
Lang LearnColin wrote:
> I'm trying to extract data from our Accounting Database and use it in
> another database that's used for our web site. Last month I created
> a SQL Select Query to give me the product information I need to put
> into our web site database. I then used DTS to copy that data from
> one database to another. Now I need to update my web site database
> with any new products that have been added into our Accounting
> database without changing any existing rows in the web site database.
> Basically, I just need to be able to add a new row for a product if
> the product ID (primary key) isn't anywhere in my web site database.
> Here's my original query to retrieve product info from the Accounting
> database: SELECT DISTINCT IV00101.ITEMNMBR AS ID, IV00101.ITEMDESC AS
> Title,
> IV00101.USCATVLS_2 AS Category
> FROM IV00101 LEFT JOIN AARG_Inv_UserDef_Item ON IV00101.ITEMNMBR =
> AARG_Inv_UserDef_Item.ITEMNMBR
> WHERE ((IV00101.ITEMTYPE)=1 AND ((IV00101.USCATVLS_2)<>'box' And
> (IV00101.USCATVLS_2)<>'replicator'
> And (IV00101.USCATVLS_2)<>'components' And
> (IV00101.USCATVLS_2)<>'displays' And (IV00101.USCATVLS_2)<>'Dist
> Audio'
> And (IV00101.USCATVLS_2)<>'Dist Video' And (IV00101.USCATVLS_2)<>'Dist
> Games' And (IV00101.USCATVLS_2)<>'Dist Softw'
> And (IV00101.USCATVLS_2)<>'Dist Books' ))
> ORDER BY IV00101.ITEMNMBR;
> RESULTS
> 40139 James Earl Jones reads the Bible (CD/Small)
> Devotional
> 40151 In Their Own Words: Space Race (CD/Small)
> Spoken Word
> 40155 Old West Collection (CD/Small)
> Spoken Word
> 40159 Lewis & Clark Collection (CD/Small)
> Spoken Word
> 40162 Ingles (CD/Large)
> Lang Learn
Have a look at NOT EXISTS to insert only rows that do not exist. ORDER
BY clauses will just add unnecessary overhead unless you are inserting
in the destination table's clustered index order.
SELECT
COL1,
COL2
FROM
dbo.SOURCE_TABLE
WHERE
COL3 = 5
AND NOT EXISTS (
SELECT * FROM dbo.DESTINATION_TABLE WHERE DESTINATION_TABLE.COL4 =
SOURCE_TABLE.COL4)
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thank you! Both of your recommendations have helped me out. Here's my
final query
I ended up using the Not Exists query
AND NOT EXISTS (SELECT * FROM TopicsWeb.dbo.tblProduct WHERE
TopicsWeb.dbo.tblProduct.Product_ID = IV00101.ITEMNMBR);
Put the above into a Insert INTO statement and now I can synch the two DB's
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uX4PPvC$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Colin wrote:
> Have a look at NOT EXISTS to insert only rows that do not exist. ORDER BY
> clauses will just add unnecessary overhead unless you are inserting in the
> destination table's clustered index order.
> SELECT
> COL1,
> COL2
> FROM
> dbo.SOURCE_TABLE
> WHERE
> COL3 = 5
> AND NOT EXISTS (
> SELECT * FROM dbo.DESTINATION_TABLE WHERE DESTINATION_TABLE.COL4 =
> SOURCE_TABLE.COL4)
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com