SQL insert question...

  • Thread starter Thread starter Brad Pears
  • Start date Start date
B

Brad Pears

I have a doozy of an SQL statement question... Not sure if it can be done
one SQL statement or not...

I have two tables... A customers table and a Notes table. They are related
in a one->many relationship based on the customer #. There are many notes
for one customer record.

For the sake of simplicity I'll shorten up the tables..

Customers: CustID, CustStatus, SalespersonName
Notes: CustID, NoteDate, NoteDesc

I was just asked this morning if I can mass insert notes into the notes
table for all customers belonging to a particular salesperson, and only for
customer status <> "Dead Lead"...

Basically the statement would read something like this ...

insert into notes (fields and values) where (select * from customers where
customers.SalespersonName = 'Joe Shmoe' and customers.CustStatus <> 'Dead
Lead').

Basically the statement above (I know it is not correct) would insert a
notes row for EACH customer that belongs to a particular salesperson where
those customer records are <> 'Dead Lead' status.

The one problem is this... To insert a row into the notes table, one must
know the actual CustID to insert into the notes row (CustID field) because
that is how the customers and notes tables are related...

Does anyone have any ideas on what an SQL statment would look like to do
this or will I have to write a procedure where I would have a recordset
containing only the customers I want to work with and then loop through that
recordset inserting notes records as I go??

Thanks,

Brad
 
The syntax should be something along the lines of

INSERT INTO Notes (custId, noteDate, noteDesc)
SELECT custId, Date(),"This is my note"
FROM Customers
WHERE SalespersonName = 'Joe Shmoe' AND CustStatus <> 'Dead Lead'

Hope This Helps
Gerald Stanley MCSD
 
Back
Top