insert with multiple conditions

[HomerJS]

I have three tables (Order, City, Company) which contain the fields:

Order
OrderNo
ShipperName
ShipperCity
ShipperState

City
ID = numeric field
Name = actual name of the city

Company
Name = Order.ShipperName
Code = left(Order.ShipperName) + left(City.Name,3)
City = City.ID


I am trying to add new Shippers from the Order table into the Company table. However, some shippers already exist in the Company table.

Also, the Order.ShipperCity field can contain either an actual city, or a numeric value. I want to only select those records that contain a numeric value.

If I need to clarify more let me know and I'll do my best.

 

[JohnDTampaBay]

How does this work for you:

INSERT INTO Company (Name, Code, City)
SELECT o.ShipperName AS Name
  , Left(o.ShipperName,3) + Left(c.Name,3) AS Code
  , c.ID
FROM Order o
   INNER JOIN City c ON c.ID = o.ShipperCity
WHERE IsNumeric(o.ShipperCity) = 1
AND NOT EXISTS (SELECT 1 FROM Company
   WHERE Code = Left(o.ShipperName,3) + Left(c.Name,3))

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[HomerJS]

Thanks Angel!! I was pretty close but didn't quite have it.