Results 1 to 9 of 9

Thread: sTORED pROC

  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: sTORED pROC

    I need a stored procedure, which should basically check if the table is empty, if yes, Insert a record , if not then dont do anything..

    In simple terms,

    If count(*) from table = 0, insert row
    and if count(*) > 1, dont do anything.


    Thanks
    Win

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Why not just use the MERGE command instead? That will be a much more efficient.

  3. #3
    Join Date
    Jun 2011
    Posts
    6

    Stored Proc

    Need better answers..its urgent

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Better? MERGE was the best answer for your question. If you started reading when Shammat suggested what to do, you'd already have it done.

    Besides, your requirement lacks some information. For example, what kind of a record would you want to insert into a table? Which table? What do you want to do when count is equal to 1?

  5. #5
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Use
    Declare @recordcnt
    select @recordcnt = count(*) from yourtable

    if @recordcnt > 0
    BEGIN
    ---
    END
    ELSE
    BEGIN
    ---
    END

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jassi.singh View Post
    Declare @recordcnt
    select @recordcnt = count(*) from yourtable
    That is not a valid Oracle syntax

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    insert into insert_table(col1,col2,col3,col4)
    select col1,col2,col3,col4
    from other_table a
    where not exists
    (select null
    from insert_table b
    where a.col1 = b.col1);
    col1 should be the primary key on both tables.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Bill, you are missing an "R" at the end of your "User title".

  9. #9
    Join Date
    Sep 2011
    Posts
    20
    If count(*) from table = 0, insert row
    and if count(*) > 1, dont do anything.
    Code:
    insert into insert_table(col1,col2,col3,col4)
    select col1,col2,col3,col4
    from other_table a
    where not exists
    (select null
    from insert_table b
    where a.col1 = b.col1);
    If I am not wrong, the above codes will insert a row into insert_table if there is no matching col1 value. If there is a matching col1 value, it will not insert a row.

    This is different from the original requirements which is much simpler because if the table is empty, then insert the row, else if the table is not empty, don't insert the row.

Posting Permissions

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