Stored Proc

[Rickspx]

I need to write a procedure for :

I have a table- tab1 which has cols- ikey,status and place.
I have another table tab2 which is to be updated/inserted which also has the same cols and more
A row present in tabl1 where the ikey is same as that in tab2 and has tab1.status = a and tab1.place = s
and if that row is not present in tab2 it has to be inserted otherwise it has to be
updated with values from tab1

Can some one plz help

Thanks
rick

 

[rac2]

Is this a production process or a one-time-only?

Do you want to limit the updating and inserting to rows where table1.status = a and table1.place = a?

Are there other columns that you want to move from table1 to table2 but limited to place = s and status = a?

/*One-time-only process*/
UPDATE table2 SET
      date_created = table1.date_created,
      volume =tab1.volume
FROM table1, table2
WHERE table1.ikey = table2.ikey
AND table1.status = 'a'
AND table1.place = 's'

INSERT table2 (date_created, volume)
SELECT date_created, volume
FROM table1
WHERE table1.ikey NOT IN (
         SELECT ikey
         FROM table2
         WHERE table1.status = 'a'
         AND table1.place = 's'
         )

Do you need a stored procedure that uses a parameter value for ikey?

/*Production process*/
CREATE PROCEDURE [fill_two_from_one](
   @vkey INT
)
AS

UPDATE table2 SET
      date_created = table1.date_created,
      volume =table1.volume
FROM table1, table2
WHERE table1.ikey = table2.ikey
  AND table1.status = 'a'
  AND table1.place = 's'
  AND table1.ikey = @vkey

IF @@ROWCOUNT = 0

INSERT table2 (ikey, status, place, date_created, volume)
SELECT ikey,
       status,
       place,
       date_created,
       volume
FROM table1
WHERE table1.ikey = @vkey