SQL Server Performance

conditionally inserting a bunch of rows into a tab

Discussion in 'T-SQL Performance Tuning for Developers' started by femig, Sep 11, 2004.

  1. femig New Member

    have a bunch of rows (the result of a query) which i want to insert into a table called TableA..

    i want to loop through all of them and only insert those not found in TableB..


    I think i have the inserting part but my problem is the looping bit..
    thanks..i dont want to do this with a cursor..
    --------------------------------------

    if not exists (select uniquekey from TableA whereuniquekey = @keyfrom_my_view)
    BEGIN
    INSERT INTO TableA select * from my_view where keyfrom_my_view = @keyfrom_my_view
    END

    --------------------------

    The "not exists" part is important to avoid inserting duplicates and saves me having to do error handling..
    thanks again.

  2. Chappy New Member

    you can do this without a loop

    insert into tableA
    (field1, field2, field3)
    select
    field1, field2, field3
    from
    tableB b
    left join tableA a ON (a.field1 = b.field1 and a.field2 = b.field2)
    where
    a.field1 IS NULL
  3. femig New Member

    i have several rows in each record to insert..it couldget very messy..
    any suggestions for an INSERT INTO SELECT * FROM solution?
    thx
  4. Chappy New Member

    Can you be clearer? I dont understand
  5. femig New Member

    the structure of both tables are identical..so how can i cut out writing all the field names? i have about 20 fields in each table..
    thx
  6. Chappy New Member

    you cant. using an insert..select you must quote each field name.
    but you could select name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'MyTable' and then cut and paste and reformat
  7. femig New Member

    thx,
    in your answer, you put "a.field1 IS NULL".
    i dont have any null field..
    can you pls explain this as i see this a lot in left outer join examples..
  8. sundeip New Member

    Do u have any primary key on your table TABLEA & TABLEB ?
    With the Help of That PK ( Primary Key ) , u can identify the rows which has not been inserted from TABLEB to TABLEA


    Insert Into TABLEA
    SELECT B.*
    FROM
    TABLEB B Left Join TABLEA A on
    B.PKID=A.PKID

    Or U can Use 'IN' Clause Also

    Insert Into TABLEA
    SELECT * FROM TABLEB Where
    PKID NOT IN ( SELECT PKID FROM TABLEA)

    PKID Refers 2 ur Primary Key/s of that tables.

    Thanks,
    Sandy
  9. sundeip New Member

    Sorry in the First Query i forgot to put one more condition. that is follows

    Insert Into TABLEA
    SELECT B.*
    FROM
    TABLEB B Left Outer Join TABLEA A on
    B.PKID=A.PKID AND
    A.PKID Is NULL

    Thnx
    Sandy
  10. femig New Member

    i have a primary key on the table i want to insert into..
    no primary key on the table i am inserting from..

    i also have duplicates in the table i am inserting from..i want to insert the first and prevent the others..
    thx
  11. sundeip New Member

    When u dont have Primary Key on TABLEB,Then How do u identify the records which have been transfered?
    &
    If Primary Key Is There on TABLEA ( the Table Inserting INTO ) and There Is no PK On TABLEB . so your data must be in tables somewhat like as follows..

    TABLEA
    ------
    ID,NAME
    1,x
    2,y
    3,z

    TABLEB
    ------
    ID,NAME
    1,x
    2,y
    2,y
    3,z
    4,a
    4,a
    4,a
    5,B
    In This case You Can Try It.

    Insert Into TABLEA
    SELECT DISTINCT B.*
    FROM
    TABLEB B Left Outer Join TABLEA A on
    B.PKID=A.PKID AND
    A.PKID Is NULL

    Or
    Insert Into TABLEA
    SELECT Distinct * FROM TABLEB Where
    PKID NOT IN ( SELECT PKID FROM TABLEA)

    Thnx
    Sandy










  12. femig New Member

    the tables are identical in structure..
    the difference is that in tableB i have a primary key on field A
    in tableA,i have no primary key on that field..
    thx
  13. sundeip New Member

    In This case You Can Try It.

    Insert Into TABLEA
    SELECT DISTINCT B.*
    FROM
    TABLEB B Left Outer Join TABLEA A on
    B.PKID=A.PKID AND
    A.PKID Is NULL

    Or
    Insert Into TABLEA
    SELECT Distinct * FROM TABLEB Where
    PKID NOT IN ( SELECT PKID FROM TABLEA)

    Or

    If ur problem doest get solved then Please give some data with the Example.


    Thnx
    Sandy

  14. femig New Member

    thx sandy,
    you have been very patient and helpful but i have 2 more questions 4 u..

    in your second statement

    Insert Into TABLEA
    SELECT Distinct * FROM TABLEB Where
    PKID NOT IN ( SELECT PKID FROM TABLEA)

    i am basing my distinct nature on 2 fields (phonenumber and transactype) other fields maybe different..i guess the SQL above may change..any help pls?

    my next question is , performance wise how does NOT IN far compared to a JOIN?
    thx
  15. sundeip New Member

    In Answer of your first question. If Your records are distinct on 2 fields only , then which value u want to take of other columns ( last one or first one or ... with any specific condition? ). on that base your query will get change. U can use Max Or Min clause to get the Values with the group by of those 2 fields.

    And in Answer of your second question. Joins would be always better than 'NOT IN ' Clause.

    For better understanding , u go through the following links , i hope this will help u .

    http://www.sql-server-performance.com/transact_sql.asp
    http://www.sql-server-performance.com/optimizing_indexes.asp
    http://www.sql-server-performance.com/query_analysis_tuning.asp







    Sandy

    (DB Developer)
  16. femig New Member

    my conditions for duplicates in tableA are transactype=N and fonenumber..
    once the fonenumber are the same and transactype=N , then i consider it a duplicate..all the other fields (about 20 others) could be the same or different..the most important thing are those 2 fields..
    i guess i should have created a composite index on those fields..
  17. sundeip New Member


    If Other Fields are not so important then u can use transfer just only that two fields.

    Or

    U can Use the following Querry

    Insert Into TABLEA
    SELECT B.*
    FROM
    (Select TransactType,fonenumber,max(Fld1),max(Fld2)...,max(Fld20)
    From TABLEB Group By TransactType,fonenumber)B
    Left Outer Join TABLEA A on
    B.TransactType=A.TransactType AND
    B.fonenumber=A.fonenumber AND
    Where
    A.TransactType Is NULL And
    A.fonenumber Is NULL

    Thnx
    Sandy

Share This Page