Locking

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • qhjghz
    New Member
    • Aug 2007
    • 26

    #1

    Locking

    I have two tables say A_prod & A_staging. Now, I need to insert those rows in A_prod which are present in A_staging but not in A_prod. We can do this in chiefly two ways :-

    1. We can simply insert the rows from A_staging to A_prod in an insert into select statement (but that would need to compare the two tables for non-matching values while performing the insert, thereby locking the table)

    2. We can find out those rows which are non-matching, insert the data in a temp table and then
    insert into A_staging select * from temp_table
    (That would also lock the table, but probably for less time)

    Now, which one's better? is "insert into A_staging select * from temp_table" a NOLOGGGING operation?
  • Krishna Ladwa
    New Member
    • Aug 2007
    • 3

    #2
    I'm not sure what do you mean by locking......

    Anyways the first choice look good.
    For ex:
    Insert into tbl_test1
    SELECT col_Test2
    FROM tbl_test2 (nolock)
    WHERE NOT EXISTS
    (SELECT Col_test1
    FROM tbl_test1 (nolock)
    WHERE tbl_test1.Col_t est1 = tbl_test2.Col_t est2)


    Krishna :)

    Comment

    • VBPhilly
      New Member
      • Aug 2007
      • 95

      #3
      Originally posted by qhjghz
      I have two tables say A_prod & A_staging. Now, I need to insert those rows in A_prod which are present in A_staging but not in A_prod. We can do this in chiefly two ways :-

      1. We can simply insert the rows from A_staging to A_prod in an insert into select statement (but that would need to compare the two tables for non-matching values while performing the insert, thereby locking the table)

      2. We can find out those rows which are non-matching, insert the data in a temp table and then
      insert into A_staging select * from temp_table
      (That would also lock the table, but probably for less time)

      Now, which one's better? is "insert into A_staging select * from temp_table" a NOLOGGGING operation?

      I'de use Query Analyzer to get an execution plan.

      Easy as pasting in your query and hitting the Estimate Execution Plan button.

      For more information:

      Comment

      Working...