Insert statement

[kbsc]

I have not created many insert statements and I'm not sure how this one should be coded. Please help.

I need to insert a record based on a select query that will give me a record set of what needs to be inserted.

select query will give me intCaseID from tblCase.

12345
23456
34567

Then I need to insert into tblIssuesOutstandging, the results would be this

12345, 0, 1/2/2008, 'Case review REI SMK', 1/1/1900, 'TES_21400', GetDate(), 1/1/1900, 'RI', 0

All insert value will be the same except the intCaseID from tblCase.

Here's the select query:
SELECT c.intCaseID FROM tblCase c
WHERE
c.dteStatusAsOf < CONVERT(DATETIME, '2008-01-01 00:00:00', 102)
AND c.intStatus = 1
AND (c.intType = 7 OR (c.intType = 8 AND c.intPossibleAsset = 1))
AND c.intDeleted = 0 AND c.vchTrustee = 'RI'

Here's what I have for the Insert query but I know it doesn't work because I don't know where or how to use the select query in the insert stmt:

INSERT tblIssuesOutstanding (intCaseID, intDeleted, dteIssuesOutstanding, txtDescription,
dteHearingOrSale, vchCreatedBy, dteCreated, dteEdited, vchTrustee, intCustomTypeID)
SELECT c.intCaseID
FROM tblCase c
WHERE
c.dteStatusAsOf < CONVERT(DATETIME, '2008-01-01 00:00:00', 102)
AND c.intStatus = 1
AND (c.intType = 7 OR (c.intType = 8 AND c.intPossibleAsset = 1))
AND c.intDeleted = 0 AND c.vchTrustee = 'RI'

VALUES
(c.intCaseID,
0, CONVERT(DATETIME, '2008-01-02 00:00:00', 102), 'Case review REI SMK',
CONVERT(DATETIME, '1900-01-01 00:00:00', 102), 'TES_21400', GetDate(),
CONVERT(DATETIME, '1900-01-01 00:00:00', 102), 'RI', 0)

Thanks for your help in advance!!!

 

[r937]

INSERT 
  INTO tblIssuesOutstanding 
     ( intCaseID
     , intDeleted
     , dteIssuesOutstanding
     , txtDescription
     , dteHearingOrSale
     , vchCreatedBy
     , dteCreated
     , dteEdited
     , vchTrustee
     , intCustomTypeID )
SELECT c.intCaseID
     , 0
     , '2008-01-02'
     , 'Case review REI SMK'
     , NULL
     , 'TES_21400'
     , GetDate()
     , NULL
     , 'RI'
     , 0
  FROM tblCase c
 WHERE c.dteStatusAsOf < '2008-01-01'
   AND c.intStatus = 1
   AND ( 
       c.intType = 7 
    OR ( c.intType = 8 AND c.intPossibleAsset = 1 )
       )
   AND c.intDeleted = 0 
   AND c.vchTrustee = 'RI'

you do not need to convert '2008-01-02' into a DATETIME, if it is involved with a DATETIME column the database will treat this as a datetime value

also, please do not use a "dummy" date like '1900-01-01', use NULL instead

r937.com | rudy.ca

 

[kbsc]

It worked perfectly thanks!