Ideally the solution will be a join between two tables in two servers..
I have a problem that I have been unable to resolve. Basically I need to retrieve a code from a database. There are less than 500 codes in a database containing thousands of records. There are tables involved.
One,table A has a code as part of the record such as:
The other, table B, has records with two columns of interest. Here they are:
Code A is used to match the numbers after sitenamepl^ in column location in order to retrieve codeB. (No I did not design this.)
Code A will probably (but not neccessarily) match one of the sitenamepl^nnnnn constructs such as sitenamepl^63506. You should note that there may be more than one sitenamepl^nnnnn entry in the column location.
Each table is on a different sql server and to match requires a collate database default.
Finding the nnnnn after sitenamepl^nnnnn in location for the match with codeA in order to find codeB in an efficient manner is another issue since ideally the code will numerous times since the table containing codeA is updated constantly. The table containing codeB and location is a reference table and is not updated frequently.
The objective and acceptable solution is efficient sql to complete a match of codeA to the numbers following sitename^ in order to obtain codeB. Ideally this be in a join between the two tables.
Only sql code is acceptable for inclusion in a stored procedure under sql 2005 to be upgraded to sql2008.
At this time I merely use a version of fnsplit and cross apply in what amount to a two step solution by building a separate reference table for location for the match . looking for something more sophistocated.