January 21, 2009 at 6:59 pm
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?
January 21, 2009 at 9:30 pm
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
January 22, 2009 at 6:18 am
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