Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Avoiding insertion of duplicate rows Expand / Collapse
Author
Message
Posted Tuesday, August 09, 2011 12:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:07 PM
Points: 52, Visits: 187
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
Post #1157112
Posted Tuesday, August 09, 2011 12:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
Insert into base...

SELECT FROM qry .... WHERE NOT EXISTS (SELECT * FROM base where qry.id = base.id)
Post #1157114
Posted Wednesday, August 10, 2011 12:55 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Read about the MERGE statement.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1157942
Posted Wednesday, August 10, 2011 3:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1158099
Posted Wednesday, August 10, 2011 9:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 26, 2012 12:52 AM
Points: 14, Visits: 199
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...
Post #1158181
Posted Thursday, August 11, 2011 3:39 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 5:55 PM
Points: 957, Visits: 1,027
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.
Post #1158855
Posted Tuesday, October 23, 2012 9:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 06, 2013 3:38 AM
Points: 12, Visits: 12
hi

procedure with Example.

Avoid Duplicate Insertion using sql server
Post #1376310
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse