Insert From Query failure
-
There are times I would like to use an "Insert From" query to populate a table but there may be records i want to append that are already present, in which case the query and stored procedure fail. In the past I have got around this by using a cursor to loop through the records to append, check to make sure the record does not already exist (If not exists()), and then insert the record if it doesn't. Is there a better way to do this? I can't use a "where keyfield not in(select keyfield from tableinquestion)" because there is more than one key field in the table.
any help is appreciated
-
you might want to perform a left join or a not exists.
insert into tableB
select A.* (I hate * but in this case maybe ...)
from tableA A
left join TableB B
on A.Keycol1 = B.keycol1
and A.Keycol2 = B.keycol2
...
where B.Keycol1 is null
OR
insert into tableB
select A.* (I hate * but in this case maybe ...)
from tableA A
where not exists (select *
from tableB B
where B.Keycol1 = A.keycol1
and B.Keycol2 = A.keycol2
.... )
Test it and choose wizely
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Can't you simply merge the 2 logics?? >>
Insert into a (key1,key2,data) (Select Key1, Key2, data from tblB B where not exists (Select * from a A where A.key1 = B.Key1 and A.Key2 = B.Key2....)).
If that doesn't help you, can you post the query and tables definition with some sample data so I can find out exactly what you need.
-
Yes, the not exists criteria within the query seems to work. Don't know why I didn't consider doing that...thanks guys!
-
Been there, done that... it's always so simple once you have the solution 🙂
-
sometimes even a DBA has to proove (s)he's only human
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply