Exists Function? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Exists Function?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-19 : 14:56:41

Here is my dilema. I want to take data from two different tables and insert it into one table, if that case doesn't already exist. This is what I have so far, but it doesn't work right:

 IF NOT EXISTS(SELECT * from tblCapRec) 
INSERT INTO tblCapRec (CaseNumber,PartNumber) 
SELECT t1.CaseNumber,1 FROM tblAbove1500 as t1 INNER JOIN tblAccurint_Above1500 as t2 ON t1.CaseNumber = t2.acctno

When tblCapRec is empty this will work, but otherwise it won't with new data. What am I doing wrong? If I haven't been clear, ask me questions. Thanks for your time!!!

Brenda

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-19 : 15:40:01

do you want to help us out with a little formatting?

please hit ENTER a few times, indent, and make your code readable. And please don't leave long lines that cause the screen to scroll way to the right.

thanks

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-19 : 16:07:21

thanks.

what is the PK of the table you are inserting into? how do you know if what you are about to insert already exists?

it will be something like this:

insert into TableA
select *
from TableB
where TableB.ID NOT IN (Select ID from Table A)

the bold part is the key part ... you can run this over and over and it will only add new rows to TableB from Table A, as long as the ID column is how you can uniquely identify each row.

another way is like this:

insert into TableA
select *
from TableB
left outer join Table A
on TableA.ID = TableB.ID
where TableA.ID is null

here you do a left outer join to table A, again using the ID column as the relationship, and insert only rows in which the outer join does not return a match (TableA.ID is null). It looks odd to insert into and query from the same table in the same statement, but that's logically how you need to do it.

can you apply this to your situation? (read up on outer joins if needed)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources