Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303

    Unanswered: is record exist ?

    hi,
    I would like asked an opinion about the system of my coding if is ok? I have one table here to modify. My point here is to count the record to avoid a duplicate record .
    rs.open "Select count (field) as name from table Where field='001'",cn
    If rs!name > 0 then
    Update sqlstatement
    Else
    Insert sqlstatement
    Endif

    Any suggestion pls..

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Be certain that your query can use an index, since that will make more difference than anything else you can do.

    Consider making a single query to both test for the existance of the row and actually do the insert/update. This is a balancing act, since this can either save or waste time depending on how you construct it. A stored procedure is probably your best bet to get the best performance.

    If you want to push the performance limit, you can use something like:
    Code:
    CREATE PROCEDURE foo
       @piFooID INT
    ,  @pcFooStuff NVARCHAR(20)
    AS
    
    UPDATE foo
       SET fooStuff = @pcFooStuff
       WHERE  fooID = @piFooID
    
    IF 0 = @@rowcount
       INSERT foo (
          fooID, fooStuff
          ) VALUES (
          @piFooID, @pcFooStuff
          )
    
    RETURN
    -PatP

  3. #3
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    Thanks Pat,

    Pat I Would Like To Asked You What Is The Best Website That Discuss About Tsql / Stored Proc?

    Popskie,

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unfortunately that question is subjective... The "best" website is the one that addresses your needs, and no two people have exactly the same needs. Heck, over any significant time span, even one person will have different needs!

    The short answer boils down to whatever site helps you the most is the best for you. This will change over time, as both you and the available web sites change. Todays answer will mean practically nothing a year from now.

    -PatP

Posting Permissions

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