Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Calgary, Alberta

    Unanswered: query insert and predicate on the same object

    Hey all,

    I've a querie which inserts into a table, while joining on it. Something like:

    insert into table1
    select (columnA,columnB)
    from table2
    where columnA NOT EXIST(SELECT 1 from table1,table2 where table1.columnA!=table2.columnA)

    As a rule of thumb, I avoid subselects and negative logic. However, sql2k seems to pick the fastest execution plan using this query, using a left anti semi join on that subselect. My question is this; what are the repercussions of inserting into a table when sql2k has already picked a query plan based on its stats? Is deadlocking possible? I haven't been able to find any evidence one way or the other. My objects are several million rows, so I am expecting some sort of side effect.

    Any help would be greatly appreciated.

    There is nothing more helpless and irresponsible than a man in the depths of an ether binge. -HST

  2. #2
    Join Date
    Aug 2002
    Have you referred to the execution plan on QA?
    Try to insert rows in batches and perform regular Tlog backups to avoid transaction log growth.
    --Satya SKJ
    Microsoft SQL Server MVP

  3. #3
    Join Date
    Nov 2002
    Quote Originally Posted by Kilka101
    As a rule of thumb, I avoid subselects and negative logic.

    Why? What put that notion in your head?

    You need to look at the plan...

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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