Results 1 to 4 of 4

Thread: Insert Problem

  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: Insert Problem

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    try this

    --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

  3. #3
    Join Date
    Nov 2003
    Posts
    2
    Thanks for your quick reply. The code falls over on line 1 Invalid object name #b. Thanks for your help

  4. #4
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    well you in trouble

    if you cant figure to replace #b with routing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •