insert into table criteria

  • Hi all,

    I am trying to accomplinsh this scenerio but logic does not get fit in my query:

    I am trying to run procedure to insert data in to table 1 or table2.

    here is the loging.

    I want to insert data in table2 if table1 has records then insert value in table2 and after that truncate table2

    and if table2 has records then insert in to table1 and then truncate table2:

    logic:

    if((select count(id) from table1) > 0))

    begin

    insert into table2 (......)

    from file....

    truncate table table1

    end

    if((select count(id) from table2) >0)

    begin

    insert into table1 (....)

    from file (.......)

    truncate table table2

    end

    by this logic it run infinite!! What should i do?

  • From your code, the first IF statement says if table1 has records, insert them into table2 and truncate table1.

    For example: table1 - has records. Then at the end of this execution,

    table2 - will have records,

    table1 - no records.

    From second IF statement, if table2 has records, insert them into table1 and truncate table2.

    Now, from above example,

    table1 - has NO records

    table2 - has records

    On execution of the second If statement,

    table2 - will have records,

    table1 - no records.

    Suppose u have put these 2 IF statements in a loop, this continues in a cycle.

    Use a else condition,

    if((select count(id) from table1) > 0))

    begin

    insert into table2 (......)

    from file....

    truncate table table1

    end

    else if((select count(id) from table2) >0)

    begin

    insert into table1 (....)

    from file (.......)

    truncate table table2

    end

  • You also might consider using EXISTS rather than a count. It will work faster

    IF EXISTS (SELECT * FROM TabA)

    BEGIN

    END

    ELSE IF...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply