DROP TABLE #Tbl1
DROP TABLE #Tbl2
DROP TABLE #Tbl3

CREATE TABLE #Tbl1 ([sid] int, cid int)
CREATE TABLE #Tbl2 (oid int, tnum varchar(10))
CREATE TABLE #Tbl3 (rec_id int identity, otnum varchar(10), ntnum varchar(10), [sid] int)

INSERT INTO #Tbl1 ([sid], cid)
	SELECT 1, 10 UNION ALL
	SELECT 2, 20 UNION ALL
	SELECT 3, 30 UNION ALL
	SELECT 4, 40 UNION ALL
	SELECT 5, 50 UNION ALL
	SELECT 6, 60 UNION ALL
	SELECT 7, 70 

INSERT INTO #Tbl2 (oid, tnum)
	SELECT 10, '100 0' UNION ALL
	SELECT 20, '2000' UNION ALL
	SELECT 30, '300 0' UNION ALL
	SELECT 40, '4000' UNION ALL
	SELECT 50, '500 0' 

-- Step1:I need to select tbl1.sid using a join of tbl1 & tbl2 based on tbl1.cid=tbl2.oid-working fine
-- step2:All the selected sid's need to be inserted into Tbl3-woking fine
INSERT INTO #Tbl3 ([sid])
	SELECT tbl1.[sid] 
	FROM #tbl1 tbl1
	INNER JOIN #tbl2 tbl2 ON tbl1.cid = tbl2.oid

-- step4:insert each updated record into Tbl3-stuck???
INSERT INTO #Tbl3 ([sid], otnum) 
	SELECT oid, REPLACE(tnum, ' ', '') 
	FROM #Tbl2 Tbl2 
	WHERE tnum LIKE '% %'

-- step3:After this I need to run an update query to remove spaces from tbl2.tnum & 
UPDATE #Tbl2 
	SET tnum = REPLACE(tnum, ' ', '') 
	WHERE tnum LIKE '% %'

SELECT * FROM #Tbl3
SELECT * FROM #Tbl2