January 21, 2015 at 8:00 am
Hi All,
I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.
BEGIN
SET NOCOUNT ON;
WITH Darin_Import_With_Key
AS
(
SELECT [pra_id]
,[pra_ClientPracID]
,[pra_NPI]
,[pra_TIN]
,[pra_ClientSystemOfRecordID]
,[Market]
,[pra_FirstName]
,[pra_LastName]
,[Mailing Address 1]
,[Mailing Address 2]
,[Mailing City]
,[Mailing State]
,[Mailing Zip]
,[sit_PrimarySiteFlag]
,[Mailing Order]
,[Office Address 1]
,[Office Address 2]
,[Office City]
,[Office State]
,[Office Zip]
,[sit_PrimarySiteFlag 2]
,[Office Order]
,[Provider Group Name]
,[Provider Group Status]
,[Provider_Group_Address1]
,[Provider_Group_Address2]
,[Provider_Group_City]
,[Provider_Group_State]
,[Provider_Group_Zip]
,[Provider_Group_Contact]
,[Provider Group Phone]
,[Provider Group Fax]
,[Provider Group Email]
,[pra_id]+[Market] AS KEY1
,GETDATE() AS [Original_File_Date]
,GETDATE() AS [Last_Update_Date]
FROM [dbo].[Import_Darin_Address_File]
)
UPDATE T1
SET T1.[pra_ClientPracID] = T2.[pra_ClientPracID], T1.[Last_Update_Date] = GETDATE()
FROM [dbo].[Darin_Address_File] T1
JOIN Darin_Import_With_Key T2 ON T1.[KEY1] = T2.[KEY1]
WHERE (LTRIM(RTRIM(T1.[pra_ClientPracID])) <> LTRIM(RTRIM(T2.[pra_ClientPracID]))
AND (T2.[pra_ClientPracID]<>'' OR T2.[pra_ClientPracID] IS NOT NULL OR T2.[pra_ClientPracID]<>'NULL'))
UPDATE T1
SET T1.[pra_NPI] = T2.[pra_NPI], T1.[Last_Update_Date] = GETDATE()
FROM [dbo].[Darin_Address_File] T1
JOIN Darin_Import_With_Key T2 ON T1.[KEY1] = T2.[KEY1]
WHERE (LTRIM(RTRIM(T1.[pra_NPI])) <> LTRIM(RTRIM(T2.[pra_NPI]))
AND (T2.[pra_NPI]<>'' OR T2.[pra_NPI] IS NOT NULL OR T2.[pra_NPI]<>'NULL'))
END
January 21, 2015 at 8:09 am
You can only use the CTE in the first UPDATE statement.
You either have to define the CTE again, or use a temp table or a view. (or a table variable)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 21, 2015 at 8:11 am
Thank you very much....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply