Solved

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

Posted on 2010-09-15
20
358 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:zintech
  • 10
  • 6
  • 4
20 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 33681931
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
 

Author Comment

by:zintech
ID: 33682027
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
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
ID: 33682088
Try this.

insert into NFL
select Team, Number
from [All-Pro]
where p.number not in (Select number from NFL)
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33682141
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
 

Author Comment

by:zintech
ID: 33682182
To relate this example to what I am doing, the "Team" field would be passed in as a parameter to the query
0
 
LVL 7

Expert Comment

by:tlovie
ID: 33682214
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
 

Author Comment

by:zintech
ID: 33682503
I am hoping this works, I just have some errors to clean up on the page I am on
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33682564
it will work. :-)
After all, u come to Expert-Exchange Site.....

Definitely you will get what u wanted.
0
 

Author Comment

by:zintech
ID: 33683192
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33683289

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:zintech
ID: 33683953
No, there are no null values.  This query is still not adding some valules to what it should be.  
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 500 total points
ID: 33684127
Der could b posibilty dat dat no.alrdy in table.pls chk dat
0
 

Author Comment

by:zintech
ID: 33684651
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
 
LVL 7

Expert Comment

by:tlovie
ID: 33684747
What columns are available in the tables NFL and All-Pro ?
0
 

Author Comment

by:zintech
ID: 33684912
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
 

Author Comment

by:zintech
ID: 33684998
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
 

Author Comment

by:zintech
ID: 33691053
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
 

Author Comment

by:zintech
ID: 33691712
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691799
hi....

if our solution is not worked then u can absolute the question.
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33691833
sory its obsolete.:-)

0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now