Searching SQL DB for Updates with another DB

doyler442

Hi

I'm using Visual Studio 2005, C# and SQL Server

I have 3 forms, each with their own DB so the structre is this:

Form = CRM, DB = CRMDB

Form = OurPlatform, DB = PlatformDB

Form = Accounting, DB = AccountDB

A user inserts data to the CRMDB through CRM but I than want OurPlatform to look through the CRMDB and any new information that has been put into CRMDB to be added to the PlatformDB

What command could I use to achieve this?

nialo

select * from server.database.schema.table

then what ever logic you need to add to compare things.

amen

hmm as nialo says the simplest is just to select but what happens if existing data is modified or even removed?

or if the data gets to CRMDB but never gets to PlatformDB?

Could you use a linked server and use and so to insert to both dbs?

Would one unified DB be better and let each form point to a different table/view?

Ginger

If its rare, you can use something like SQL Compare, but i think a unified db with different views would work better

doyler442

Hi

Thanks for the replies

The reason I can't use one unified DB is that the idea is we can bring OurPlatform to a company and install it without having to replace their current DBs with our own.

We just want to be able to extract information from their DB onto ours and than pass it onto another DB.

amen

We just want to be able to extract information from their DB onto ours and than pass it onto another DB

Is the data transfer a once off or daily task?
what happens if the data is modified in PlatformDB and CRMDB?
how will you know which data is correct?
what happens if there is a schema change in CRMDB which isn't catered for in PlatformDB

kayos

amen wrote: »

Is the data transfer a once off or daily task?
what happens if the data is modified in PlatformDB and CRMDB?
how will you know which data is correct?
what happens if there is a schema change in CRMDB which isn't catered for in PlatformDB

Or in short thats one big can of worms