|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Insert or Update Row if Primary key isn't pre-existing in table SQL 2000another 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 Here's a query to add new/missing rows to another table in another database:
insert into Database2.dbo.Table2 (PK, Data) select PK, Data from Database1.dbo.Table1 where PK not in (select PK from Database2.dbo.Table2) I wasn't sure if the web site database and the accounting database were on the same server. If so, the above technique would work. If not, you could add the accounting database's server as a linked server (or vice versa) and use fully qualified object names. i.e. select ...from MyLinkServer.Database1.dbo.Table1. You could also use SQL Server replication to capture add/insert/update/delete in one database and push changes to one or more other databases. Hope that helps, Joe Show quote "Colin" 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 > > > Colin wrote:
Show quote > I'm trying to extract data from our Accounting Database and use it in Have a look at NOT EXISTS to insert only rows that do not exist. ORDER > 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 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) 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 Show quote "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:uX4PPvC$FHA.2036@TK2MSFTNGP14.phx.gbl... > Colin 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  |
|||||||||||||||||||||||
Other interesting topics