March 13, 2009 at 4:41 pm
I am trying to insert into a table 89 rows that are all the same except the contactID.
I need to loop through the contactIDs that I get from this query. I am not sure how to do that.
My insert Statement:
INSERT INTO [Registration]
([ContactID]
,[CourseUniqueID]
,[CourseDate]
,[LocationID]
,[FacultyID]
,[Reg_Status]
,[OperatorLogin]
,[PaymentHistoryID]
,[WaitListFlag]
,[CourseScheduleID]
,[CourseCompletedFlag]
,[WaitListDate]
,[Course_Number])
VALUES
(@contactID
,80
,'9/26/2007 8AM'
,84
,46
,'enrolled'
,'April'
,NULL
,0
,NULL
,0
,NULL
,101)
My query to get the contactIDs:
SELECT Contact.ContactID
FROM CourseHistory INNER JOIN
Contact ON CourseHistory.ContactID = Contact.ContactID
WHERE (CourseHistory.CourseUniqueID = 80) AND (CourseHistory.CourseDate = CONVERT(DATETIME, '2007-09-26 08:00:00', 102)) AND
(CourseHistory.Course_Cost = 0)
Thank you for your assistance.
March 13, 2009 at 5:07 pm
No need to loop.
This statement will insert all 89 rows in one transaction.
INSERT INTO [Registration]
([ContactID]
,[CourseUniqueID]
,[CourseDate]
,[LocationID]
,[FacultyID]
,[Reg_Status]
,[OperatorLogin]
,[PaymentHistoryID]
,[WaitListFlag]
,[CourseScheduleID]
,[CourseCompletedFlag]
,[WaitListDate]
,[Course_Number])
SELECT C.ContactID
,H.CourseUniqueID
,H.CourseDate
,84
,46
,'enrolled'
,'April'
,NULL
,0
,NULL
,0
,NULL
,101
FROM CourseHistory H
INNER JOIN Contact C ON (H.ContactID = C.ContactID)
WHERE (H.CourseUniqueID = 80)
AND (H.CourseDate = '20070926 08:00:00')
AND (H.Course_Cost = 0)
Before running the INSERT, you can run the SELECT part of the statement to check the rows that will be inserted.
SELECT C.ContactID
,H.CourseUniqueID
,H.CourseDate
,84
,46
,'enrolled'
,'April'
,NULL
,0
,NULL
,0
,NULL
,101
FROM CourseHistory H
INNER JOIN Contact C ON (H.ContactID = C.ContactID)
WHERE (H.CourseUniqueID = 80)
AND (H.CourseDate = '20070926 08:00:00')
AND (H.Course_Cost = 0)
March 13, 2009 at 5:33 pm
SSChasing Mays,
Perfect! Thank you very much for your help.
Norbert
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply