And a bit more robust version

DECLARE @SAMPLE TABLE (ID INT NOT NULL, STRING VARCHAR(200) NOT NULL)

INSERT INTO @SAMPLE (ID,STRING)

VALUES (1,'CANADA USA''ENGLAN SPAIN'' ITALY '' FRANCE GERMANY ...

I suppose the frontend in question has some minimal checks in place so that it always passes a string with even number of quotes and no repeating blanks.

declare

This way may be

select a.Id, a.Name, b.*

from table1 as a

outer apply SearchInfoAround(a.Name, a.mobile) as b

If the question is how to master SearchInfoAround() i don't know really.

Looks like you need just self join the table

select a.*, b.Code as parentCode

from Equipment a

join Equipment b on b.Equipment_ID = a.Parent_Id

Do you need .xls file at client computer or at the sever?

If it has to be a server side procedure i see two options.

1. Use OPENROWSET.

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',

'Excel 8.0;Database=D:\222\myFile.xls;',...

The first one has a little problem.

DECLARE @dt DATETIME = '2014-04-09 23:59:59.000';

...

Trying to restate the problem

with LongestSegments (itinerary_id, segment_id, mileage_from_origin, waiting_time) as (

-- Get the 'longest' flight (AKA segment) for every itinerary.

-- No problems here.

-- Sample data

SELECT

100 as itinerary_id,

10001 as segment_id,

3174....

First a minor terminology note. RDBs operates on sets...

It still isn't clear if you are designing an end user visual interface or creating reusable piece of SQL code which other code will call or need...

Possible registry problem see https://social.msdn.microsoft.com/Forums/sqlserver/en-US/10d4ab5c-55be-4dd1-8ca7-13a1703bd9f0/sql-server-2008-installation-fails-there-was-an-error-generating-the-xml-document-error-code?forum=sqlsetupandupgrade

Also note that this requrement

If source records are available in Archive table but some of the record in source column is change, then update the old record with latestversion=0...

First build XML then cast to NVarchar and add headers, because when processing XML type MSSS treats <xml ..> not as part of data but as a directive specifying following...

Check UNICODE() of the problem symbol and collation. Under some collations ASCII=32 doesn't mean UNICODE =32 which is required for trim() functions.

Alan.B (4/22/2015)

---

I did not have time to put together a 1,000,000 row test but this guy:

SELECT

Col1 = ISNULL(a.name,b.name),

CASE ISNULL(a.name,'') WHEN '' THEN 'Table B'...

No, i mean not the first but the second...