Locking

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

[Krishna Ladwa]

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_test1 = tbl_test2.Col_test2)


Krishna :)

[VBPhilly]

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:
http://www.sql-server-performance.co...alysis_p1.aspx