• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 731
  • Last Modified:

Insert new records only

I'm trying to insert only new records into a table and wanted to know if this was the best solution.  I know if I was only comparing one field in the different tables I could do a not in (select distinct etc...) but the new value is based on two fields creating the unique value.  This is the query I'm considering using.  Any better ways of doing this?


INSERT INTO SGroups(DepartmentID, SGroupName)
SELECT DISTINCT A.Department, A.SGroup
FROM EmpData_Dump A LEFT JOIN SGroups B ON A.Department = B.DepartmentID and A.SGroup = B.SGroupName
WHERE B.DepartmentID IS NULL AND B.SGroupName is null
0
tim_cs
Asked:
tim_cs
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
Ashish PatelCommented:
This is absolutely good. Nothing better than this.
0
 
YveauCommented:
That is exactly what I would do ...

Hope this helps ...
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
another way:


INSERT INTO SGroups(DepartmentID, SGroupName)
SELECT DISTINCT A.Department, A.SGroup
FROM EmpData_Dump A 
WHERE NOT EXISTS
(SELECT 1
FROM SGroups B 
WHERE A.Department = B.DepartmentID and A.SGroup = B.SGroupName)

Open in new window

0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
tim_csAuthor Commented:
Thanks to everybody for the feedback and thank you for the other solution gbshahaq.  Is there a reason to do it that way versus the other method?  Which would be faster for larger tables?
0
 
Ashish PatelCommented:
for larger table your query would be a good performer.
0
 
JR2003Commented:
As you're using SQL Server 2005 you can make use of the new EXCEPT set operator. This is sometimes quicker for large result sets.

INSERT INTO SGroups(DepartmentID, SGroupName)
SELECT DISTINCT A.Department, A.SGroup
FROM EmpData_Dump A 
EXCEPT 
SELECT DISTINCT B.DepartmentID, B.SGroupName

Open in new window

0
 
JR2003Commented:
should have said


INSERT INTO SGroups(DepartmentID, SGroupName)
SELECT DISTINCT A.Department, A.SGroup
FROM EmpData_Dump A 
EXCEPT 
SELECT DISTINCT B.DepartmentID, B.SGroupName
FROM EmpData_Dump B

Open in new window

0
 
JR2003Commented:
What I really meant to put was:
INSERT INTO SGroups(DepartmentID, SGroupName)
SELECT DISTINCT A.Department, A.SGroup
FROM EmpData_Dump A 
EXCEPT 
SELECT DISTINCT B.DepartmentID, B.SGroupName
FROM SGroups B

Open in new window

0
 
YveauCommented:
:-) Three times is a charm !
Good pointJR2003, I came back to the thread to inform tim_cs on this option, but you beat me to it :-)
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now