I am fairly new to SQL and have been given a horrible task. I have 2 tables, table 1 is eng_routing and table 2 routing the rows in table 1 are
engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 30 now machine here
1 40 end

the rows in table 2 are

part opnumber rdesc
1 10 goodsin
1 20 treat
1 30 final

the task I have been given is to insert the ops from table 2 into table 1 where engpart=part and where table 1 has a treatments, but the data from table 2 needs to be inserted at the next number after treatments (in this example opnumber 21 and so on) for all opnumber and rdesc,so the outcome would look like

engpart engopnumber engrdesc
1 10 machine here
1 20 treatments
1 21 goodsin
1 22 treat
1 23 final
1 30 now machine here
1 40 end

I have been given the following code but an error occurs on line 7 on keyword 'where'

use efacdb
begin tran
INSERT INTO eng_routing
SELECT routing.part, TBL3.engopnumber + (SELECT count(opnumber) FROM routing T1
Where T1.part = T.Part AND T1.opnumber<=T.opnumber ) opnumber ,routing.rdesc FROM routing T
INNER JOIN (SELECT engpart,engopnumber,engrdesc FROM eng_routing
WHERE eng_routing.engrdesc= 'treatments') TBL3 where T.part=TBL3.engpart

select * from eng_routing
--commit tran
--rollback tran

Any help would be gratefully recieved

--hope this is what you are after, if not my appologies

--first select distinct opnumber into temp table
select distinct opnumber into #c from #b

--now you should be able to do your insert from a select statement like
--this
SELECT b.part, a.engopnumber + (SELECT count(c.opnumber) FROM #c c
Where b.opnumber>=c.opnumber ) opnumber ,b.rdesc FROM eng_routing a, routing b
where a.engrdesc= 'treatments'
order by opnumber

Thanks for your quick reply. The code falls over on line 1 Invalid object name #b. Thanks for your help

if you cant figure to replace #b with routing