I have 2 table: CRM_Inquiries and ICS_Subscribers. CRM_Inquiries has the primary key InquiryID and a foreign key, SubID to associate the table to ICS_Subscribers as well as Fname and Lname. ICS_Subscribers has the primary key SubID and the columns SubFirstName and SubLastName. I need to run a query to retrieve CRM_Inquiry rows and update the Fname and Lname values with the SubFirstName and SubLastName in the ICS_Subscriber table. I hope that makes sense..
I wrote a script to accomplish this but I was wondering if there was a better way to do it.
CREATE TABLE #inq(
InquiryID INT,
SubID INT
)
INSERT INTO #inq
SELECT DISTINCT InquiryID, SUbId
FROM CRM_Inquiries
WHERE LName = 'Poe' AND SubID IS NOT NULL
DECLARE @totalCount INT
SELECT @totalCount = COUNT(*) FROM #inq
PRINT @totalCount
WHILE(@totalCount > 0)
BEGIN
DECLARE @subId varchar(250)
DECLARE @inquiryID INT
SELECT TOP 1 @subId = subID, @inquiryID = inquiryID FROM #inq
PRINT 'SubID = ' + @subID
PRINT 'InquiryID= ' + CAST(@inquiryId AS VARCHAR(MAX))
UPDATE CRM_Inquiries
SET FName = (SELECT SubFirstName FROM ICS_Subscribers WHERE SubID = @subId),
LName = (SELECT SubLastName FROM ICS_Subscribers WHERE SubID = @subId)
WHERE InquiryID = @inquiryID
DELETE FROM #inq WHERE InquiryID = @inquiryID
SELECT @totalCount = COUNT(*) FROM #inq
PRINT @totalCount
END
DROP TABLE #inq
The LName='Poe' is completely arbitrary, I have a much more complex query but for the sake of the example I just created that.
