• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 733
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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