Inserting New Data from One Table to Another without Creating a Duplicate Field Value

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)

Inserting New Data from One Table to Another without Creating a Duplicate Field Value

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-21 : 09:41:55

Chris writes "I need to Update TableA with data from TableB. Both have the same table design with the field names 'FileNumber' 'OrderDate' and 'Comments'.

When Updating, I want to bring in all data from TableB without duplicating or overwriting data that already exists in TableA (based on 'FileNumber') .

I can not overwrite any existing data in TableA, due to a third party updating 'Comments' in TableA only.

Example:

TableB: (Update From)
FileNumber | OrderDate | Comments
0001       | 2/14/2002 | NULL
0002       | 2/17/2002 | NULL
0003       | 2/18/2002 | NULL

TableA: (Update To)
FileNumber | OrderDate | Comments
0001       | 2/14/2002 | Returned by Customer
0002       | 2/17/2002 | NULL

Resulting TableA:
FileNumber | OrderDate | Comments
0001       | 2/14/2002 | Returned by Customer
0002       | 2/17/2002 | NULL
0003       | 2/18/2002 | NULL

There is probably the easiest solution, but this problem has been KILLING ME!"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-02-21 : 09:55:28

Try this:


INSERT INTO TableA
SELECT * FROM TableB WHERE FileNumber NOT IN (SELECT FileNumber FROM TableA)

nr
SQLTeam MVY

12543 Posts

Posted - 2002-02-21 : 10:23:53

You might find this more efficient.

insert TableA
select b.*
from TableB b left outer join TableA a on a.FileNumber = b.FileNumber
where a.FileNumber is null

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources