How do I insert values into a table only when values don't exist?

I am trying to insert values into a table, only when a value does not already exist.  What I have is a SQL table of values, that I want to insert all of those values into another table, but only inserting each of the values when a value does not exist in the new table based on a condition.  For example, the table of preset values that I want to insert into other tables is:

Table: All-Pro

Number              Player                  
18                      Peyton Manning  
87                      Reggie Wayne  
9                        Drew Brees    
17                      Philip Rivers      
4                        Brett Favre    
12                      Aaron Rodgers  

And I want to insert all of these values into the following table, but ONLY insert the values that do NOT already exist in this table:

Table: NFL

Team                     Number
Colts                      18
Colts                      87
Colts                      44
Colts                      93
Colts                      17

So when I execute a SQL query that I am asking you guys for help with, the query would attempt to insert every value from the "All-Pro" table into the "NFL" table, and would insert every value EXCEPT for 18, Peyton Manning and 87, Reggie Wayne into the "NFL" table.  I know the query would be an INSERT INTO NFL..............  query, I just can't figure out the syntax
zintechAsked:
Who is Participating?
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Try this.

insert into NFL
select Team, Number
from [All-Pro]
where p.number not in (Select number from NFL)
0
 
tlovieCommented:
Somthing like this would probably work:

insert into NFL
select Team, Number
from [All-Pro] p
where not exists (select 1 from NFL where NFL.Number=p.Number)
0
 
zintechAuthor Commented:
The All-Pro table does not contain a field named "Team".  I am also confused about the number "1" in "SELECT 1 fron NFL WHERE NFL.Number=p.Number"
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
tlovieCommented:
you can put anything in the select statement when you are doing an exists.  select *, select [any column].  I generally put select 1 but that's just a matter of preference.

the team field has to come from somewhere.... where are we supposed to get it?

Brishsoft's solution will also work.
0
 
zintechAuthor Commented:
To relate this example to what I am doing, the "Team" field would be passed in as a parameter to the query
0
 
tlovieCommented:
So then it would look like this:
insert into NFL
select @Team, Number
from [All-Pro] p
where not exists (select 1 from NFL where NFL.Number=p.Number)
0
 
zintechAuthor Commented:
I am hoping this works, I just have some errors to clean up on the page I am on
0
 
Bhavesh ShahLead AnalysistCommented:
it will work. :-)
After all, u come to Expert-Exchange Site.....

Definitely you will get what u wanted.
0
 
zintechAuthor Commented:
It inserted SOME of the values that were supposed to be inserted, and none some other times.  Strange behavior.  Is there any way to tweak the query to make it work?
0
 
Bhavesh ShahLead AnalysistCommented:

does your Number column having null value?

if so

insert into NFL
select @Team, Number
from [All-Pro]
where isnull(number,0) not in (Select isnull(number,0) from NFL)
0
 
zintechAuthor Commented:
No, there are no null values.  This query is still not adding some valules to what it should be.  
0
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
Der could b posibilty dat dat no.alrdy in table.pls chk dat
0
 
zintechAuthor Commented:
I checked, and the numbers to be added are in the table, but they are listed under a different TEAM and the query is not adding them.  For example if 18, Manning was listed under Team Chargers then this query would not add it, even though it is supposed to since 18, Manning is not listed under the team Colts, which was the parameter passed
0
 
tlovieCommented:
What columns are available in the tables NFL and All-Pro ?
0
 
zintechAuthor Commented:
I will use the tables that I am specifically using for ease of explanation.  Table 1, which contains the values that are to be inserted into other tables, contains the columns "WBS" and "WBS_Title".  Table2, which contains several different values and it the table that the values form Table1 will be inserted into, contains columns "ProjectId", "WBS" and "WBSTitle"
0
 
zintechAuthor Commented:
What I am trying to do is insert all values for "WBS" and "WBS_Title" from table 1 into table2 for a given ProjectId and insert the records into that table where the given ProjectId does not already have a record for each individual "Project" and "ProjectId"
0
 
zintechAuthor Commented:
I have tried a few different variations of this query with the same results.  The query successfully does not add the record when there is a duplicate for it, but it does not add all of the records that it should
0
 
zintechAuthor Commented:
I found the correct solution.  The issue was that I was not joining the two tables together correctly.  So the correct syntax would look like:

INSERT INTO table2 SELECT @Project, WBS, WBS_Title FROM table1 as old WHERE NOT EXISTS (SELECT * FROM table2 WHERE table2.WBS = old.WBS AND table2.ProjectId = @Project
0
 
Bhavesh ShahLead AnalysistCommented:
hi....

if our solution is not worked then u can absolute the question.
0
 
Bhavesh ShahLead AnalysistCommented:
sory its obsolete.:-)

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.

All Courses

From novice to tech pro — start learning today.