INSERT rows with other table info and explicit data in same statement

[eseabrook2008]

I'm trying to created an INSERT statement to insert data from another table as well as explicit data. This is what I've tried:
**this statement gets all the agentid's I need**
INSERT INTO SYS2_CSIO_BROKERINFORMATION
(AGENTID)
SELECT DISTINCT agentID
FROM Sys2_AgentsAndDirectors AD
WHERE ad.companynumber = '101' and status = 'A' and agentid between '100000' AND '183000';

**this statement adds the explicit data I need**
INSERT INTO SYS2_CSIO_BROKERINFORMATION
(VENDORSYSTEM, COMPANYNUMBER, STATUSINDICATOR)
VALUES ('1', '101', '1');

I need to be able to combine the two so that with each 'agentid' added, the required explicit data is added as well. Am I able to combine the two? It won't even run the first Statement because the COMPANYNUMBER field cannot be null.

Thoughts?

 

[SantaMufasa]

ESeaBrook,

This code should work just fine for you:

INSERT INTO SYS2_CSIO_BROKERINFORMATION 
(AGENTID, VENDORSYSTEM, COMPANYNUMBER, STATUSINDICATOR) 
SELECT DISTINCT agentID,'1', '101', '1'
FROM Sys2_AgentsAndDirectors AD 
WHERE ad.companynumber = '101' and status = 'A' and agentid between '100000' AND '183000';

Let us know if there is a problem.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[eseabrook2008]

Gah! I knew I was missing something. Thanks Mufasa!