CODEWITHSUNDEEP
sql-servert-sqlif-statement
V.B
V.B

Reputation: 1211

If Exists with Insert statement Not going to Else part

My SQL query is like below

if exists(select 1 from mytable2 T,mytable M where T.bookid=M.bookid)
 begin
  insert into books(col1,col2)
  select col1,col2 from library
 end

else
 begin
  insert into booknotfound 
  select col1,col2 from library
end

say Library is a source table. I'll insert in books table only if BookId is present in "mytable" otherwise I want to insert in "booknotfound" table.the above query inserting records in books table but not in "booknotfound" table. So what I want to do is I am looking for bookid for each record in "mytable2" Is is possible this way?

Upvotes: 0

Views: 154

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

I think you're trying to do something else than what you've written/expect you've written. (I would guess).

What you're checking for is not each row in an interation, but all books at once. Remember, SQL operates on SETs of data (which can be a single row, or multiple).

So what you're doing is asking

If this returns 1

select 1 from mytable2 T,mytable M where T.bookid=M.bookid

Then insert ALL elements from Library into Books.

If it returned 0, insert ALL elements from Library into Books not found.

What I think you're after is either doing a cursor based operation (not adviceable), or doing two different setwise operations such as 

INSERT col1, col2 INTO books FROM liberary WHERE <INSERT YOUR WHERE CLAUSE>
INSERT col1, col2 INTO booknotfound FROM liberary WHERE <INSERT YOUR WHERE CLAUSE>

The specifics of your WHERE clause is difficult to deduce from what you've written as I fail to see any link between library and your "mytable" and "mytable2" (are you missing some information?)

My guess is that you're after the books not in library from mytable should be put into not found and those that are in library should be in books?

Upvotes: 2

Related Questions