August 9, 2011 at 12:02 pm
I have a table with data. I need a query that need to insert only new rows instead of whole data.
Source Table:
TableA
FName
LName
PhoneNo
Destination Table:
TableB
ID
FName
LName
PhoneNo
August 9, 2011 at 12:03 pm
Insert into base...
SELECT FROM qry .... WHERE NOT EXISTS (SELECT * FROM base where qry.id = base.id)
August 10, 2011 at 3:27 pm
If you don't care to track what the dupes are, just put a UNIQUE index on the columns with the IGNORE DUPES option and do the inserts.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 10, 2011 at 9:55 pm
insert tableb(lname,fname,phoneno)
select a.lname,a.fname,a.phoneno
from tablea a
left join tableb b on a.lname=b.lname and a.fname=b.fname and a.phoneno=b.phoneno
where b.id is null
A longer version of Ninja's suggestion...
August 11, 2011 at 3:39 pm
If all you need to do is an insert, then the INSERT INTO... with a NOT EXISTS (or a LEFT OUTER JOIN) to check that the record isn't already there should do fine. You could also do it with a MINUS, but I don't find that as flexible.
If you need to do an insert if the record doesn't exist at all, or an update if the key for the record is there but some of the other columns may differ, I'd go with Joe Celko's suggestion and look at using the MERGE statement.
Having said that, in a system I built recently, all of the data updates were done via generated MERGE statements. So from experience, MERGE works perfectly fine as just an insert.
October 23, 2012 at 9:31 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply