automatically populating the fields from one database to another

[a5b5c5]

Scneario
We have a Salesforce application that is right now being implemented to enter placement information.We also have another home grown application where we will automatically populate the placement information coming from salesforce.

Issue
1. The placement information coming from Salesforce is distributed among 3 or 4 base tables, so I need to figure out the way of importing all the fields into different tables of home grown application.

2.The ID coming from Salesforce is different than the ID that we use in homegrown application.Can anybody suggest me a way of how I can get convert the Salesforce Id into
Home grown application ID?

Your hlep would be greatly appreciated.

Thanks & Regards
----------------
Abyie

 

[Catadmin]

Depending on what version of SQL you're using, DTS or SSIS packages would probably be your best solution. Either way, I think you're going to be doing a little bit of schema re-inventing on the destination tables to get everything to fit.

For instance, if SalesForceID is an identity column, in order to get App1's SalesForceID into App2's SalesForceID, I'd recommend having 2 columns on the table, one for App2's id and the other for App1's ID.

On the other hand, if SalesForceID is manually created by App2, then you just need to change the code to accept App1's SalesForceID.

Not knowing the structure of the data I can't give you much of a better answer. Sorry.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLSister]

The best way of finding the id conversion is to look at what ever the natural key of the data is. This can be very difficult if you have names because names vary som uch in how they are entered and are not unique. You may need to match names and addresses and or phones or emails from both systems and manually match ones that are close but not exact. This may need to be done by the users of the data once you find a way to tell them which ones you think are matches.

On the otherhand, if you are just now implementing the one application, perhaps the first step should be to move the base data to the new system and build a table to match the ids at the time you do the move of the data. If this system is a commercial product, you will probably need to build the table inyour homegrown product. Then build a system that whenever new data is ported from one system to the other, the table is updated to keep it current.

Porting between systems can be very complex and difficult. You need to thoroughly understand the schema of both systems and how the data you are interested is is stored. More than likely you will have data type mismatches that will have to be addressed. If address is varch(100) in one systemand varchar(50) in another, then you will have problems when that record which is 78 characters long nedds to go to the oher system. With one system being homegrown you have the ability to at least adjust that system to make it nmore compatible with the other one.

Some of the issues you may run into are:
where one system has one to many relationships but the other is only one 2 one.
where data types not only do not mathc but are incompatible.
where bad data is stored in one system (for instance in a date field which is varchar instead of datetime which feeds to a datetime field and some of the values are not valid dates) and cannot be ported to the other
where the data doesn't have any natural keys to orgianlly link on
where there are differences in required fields resulting inthere being no data from one system to go into a required field in another
where the data is not used for a single purpose in each field and so is very difficult to configure to a system where the data cannot be stored that way.
where the format of how names are stored is differnt and diffuclt to convert such as when one system stores the whole name and another stores first_name, middle_name, last_name



Questions about posting. See faq183-874