how to insert records that does not exist 1

[shaminda]

I have two tables say TableB and TableC. Here is what’s in the two tables

TableB
fdUserLoadId fdPart fdQty fdDate
1 78500 A 2 12/12/2006
2 78500 B 3 12/12/2006
3 78500 B 5 12/12/2006
4 78500 A 5 12/12/2006
5 78500 C 6 12/12/2006
6 78500 D 5 12/12/2006
7 78500 A 5 12/12/2006


TableC
fdItemId fdUserLoadId fdPart fdQty
54 1 78500 A 2
55 2 78500 B 3
56 3 78500 B 5
57 4 78500 A 5


What I want to do is Insert the records 5,6, 7 from TableB to TableC. How do I write a SQL statement to do this?

 

[bborissov]

INSERT INTO TableC (fdUserLoadId, fdPart, fdQty) VALUES
SELECT fdUserLoadId, fdPart, fdQty
       FROM TableB
LEFT JOIN TableC ON TableB.fdUserLoadId = TableC.fdUserLoadId
WHERE TableC.fdUserLoadId IS NULL

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[shaminda]

Thanks Borislav,
I got it working, but I had to remove 'VALUES'.

 

[bborissov]

ALWAYS forgot that!
Always when I use (field list) I add values and got errors,
No matter if I use SELECT after that.
Sorry about that.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP