Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Complicated Sync.

Author  Topic  jibarra Starting Member 3 Posts Posted - 2010-11-18 : 19:42:55 Hello All. I need some help with the following situation: I have a read only database (Source.SalesTable) which I need to sync with a new table in my database. (Target.SalesTable). The source table has IDs to other tables which will not be the same to mine. To get around this I have created link tables for each filed. Hopefully I can clearly explain what I after below. SalesSource Table SaleID Int CardRecordID int ShippingAddress Varchar(Max) SaleLink Table (one to one) SourceSaleID Int TargetSaleID Int ShippingAddresLinkTable (Many to Many) SHID int Autoinc, Address VarChar(Max) CardRecordLinkTable (Many to Many) – already populated CardRecordLinkTable int Autoinc CardRecordID int CompanyID int SaleTarget Table SaleID int Autoinc, CompanyID Int ShippingID int So what needs to happen Full outter Link SalesSourceTable with SaleLinkTable on SalesSourceTable.SaleID = SaleLinkTable.SourceSaleID (not sure if this should be included in the select statement) Link SalesSourceTable with CardRecordLinkTable on SalesSourceTable.CardRecordID = SaleSouceTable.CardRecordID If SalesTargetTable.SaleID <> SalelinkTable.TargetSaleID Then Begin If ShippingAddresLinkTable.address <> SaleSource.ShippingAddress Then Insert ShippingAddress from SaleSourceTable into ShippingAddressLinkTable Return scope_identity Else Return matching ShippingAddressLinkTable Scope_identity Insert new record into SaleTargetTable with ShippingID = ShippingAddressLinkTable.scope_identity, CompanyID = CardRecordLinkTable.CompanyID End Then return the scope_identity from SaleTargetTable and SaleSourceSaleID And Insert into SaleLinkTake I have been trying to work this out for weeks now. I am currently studying for the SQL server exams but this is out of my knowledge at this point of time. If someone could point me the right direction. I would appreciated it. Thank you for your time Jorge