Move data if records do not exist

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 2005 Forums

Transact-SQL (2005)

Move data if records do not exist

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 09:19:02

What is the best way to insert data into a table if data does not exist in another table?

I was thinking of an IF not Exists statement but I am unsure on how to code it.

I am using 3 tables. oelin, OR, and ORH.

All three tables contain ord_type,ord_no,item_no,and line#.

If a record in the OR table does not exist in the oelin I want to delete it from the OR table and move it to the ORH table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 09:50:07

you can use LEFT JOIN for deleting this:-

DELETE o
FROM OR o
LEFT JOIN oelin oe
ON oe.PK=o.PK
WHERE oe.PK IS NULL

then put a delete trigger for putting record in ORH

CREATE TRIGGER CaptureHistory
ON [OR]
AFTER DELETE
AS
INSERT INTO [ORH]
SELECT * FROM DELETED
GO

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 10:48:51

The delete did not seem to do the trick. Here is what I used:

delete orderrebate
from orderrebate
left join oeordlin_sql on oeordlin_sql.ord_type = orderrebate.ord_type and
oeordlin_sql.ord_no = orderrebate.ord_no and oeordlin_sql.item_no = orderrebate.item_no and
oeordlin_sql.line_seq_no = orderrebate.line_seq_no
where oeordlin_sql.ord_type is null and oeordlin_sql.ord_no is null and oeordlin_sql.item_no is null and
oeordlin_sql.line_seq_no is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 10:51:37

quote:
Originally posted by Vack

The delete did not seem to do the trick. Here is what I used:

delete orderrebate
from orderrebate
left join oeordlin_sql on oeordlin_sql.ord_type = orderrebate.ord_type and
oeordlin_sql.ord_no = orderrebate.ord_no and oeordlin_sql.item_no = orderrebate.item_no and
oeordlin_sql.line_seq_no = orderrebate.line_seq_no
where oeordlin_sql.ord_type is null and oeordlin_sql.ord_no is null and oeordlin_sql.item_no is null and
oeordlin_sql.line_seq_no is null

do you mean it didnt delete?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 10:52:56

I was just about to reply again.
The script runs fine, but I get 0 rows affected.

So yes, the record did not delete.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:00:40

quote:
Originally posted by Vack

I was just about to reply again.
The script runs fine, but I get 0 rows affected.

So yes, the record did not delete.

that menas you dont have any records in orderrebate that dont exists in oeordlin_sql

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 11:02:19

I just double checked. Order # 612745 does not exist in the oeordlin table. But it does exist in the orderrebate table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:09:40

quote:
Originally posted by Vack

I just double checked. Order # 612745 does not exist in the oeordlin table. But it does exist in the orderrebate table.

what's the relationship b/w two tables?

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-04 : 11:13:51

I'm sorry you were right. I retested and it did remove the records when they did not exist.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 11:32:39