multiple temp tables - having problem help please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

multiple temp tables - having problem help please

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-15 : 09:19:37

i have the following procedure where i need to capture test results and feed them to a temp table.
(in blue) the first part works great i get my inital information. the problem i am having is the follow up
(in red) i create an additional temp table to feed in the follow up test results and i get an error.
Msg 2714, Level 16, State 6, Procedure NYP_PAL_CARE_rpt, Line 376
There is already an object named 'SHARON' in the database.

i know i am doing something incorrectly just cant figure where or what. please help!!!

CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
ClientDocGUID numeric(16,0),
ClientVisitGUID numeric(16,0),
PatCareDocGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
DOB DATETIME,
GENDER VARCHAR(15),
LANGUAGECDE varchar(20),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
Unit_Note_Written VARCHAR(30),
LOC_time_rpt varchar(30),
LOC_NAME VARCHAR(60),
cat_name varchar(60),
--Value_text VARCHAR(2000),
Prim_diag_description VARCHAR(60),
Username varchar(50),
User_Occupation varchar(30),
INIT_DATE_OF_RQST varchar(255),
INIT_NOTE_DATE varchar(255),
INIT_NOTE_TIME varchar(255),
REFERRING_PROVIDER varchar(255),
REFERRING_SERVICE varchar(2000),
INIT_SUPPORT_COUNSELING varchar(2000),
INIT_FAMILY_SUPPORT VARCHAR(2000),
INIT_HOSPTICE_REF_DISCUSS VARCHAR(2000),
INIT_PROGNOSTICATION VARCHAR(2000),
INIT_PAIN_MGMT VARCHAR(2000),
INIT_SYMPTOM_MGMT VARCHAR(2000),
INIT_WITHHOLD_SUSTAIN VARCHAR(2000),
INIT_DISCHARGE_PLAN VARCHAR(2000),
INIT_GOALS_CARE VARCHAR(2000),
INIT_ADVANCE_DIRECTIVE VARCHAR(2000),
INIT_PATIENT_SUPPORT VARCHAR(2000),
INIT_OTHER VARCHAR(2000))

--Get all patients who had a PALLIATIVE CARE CONSULT Note within the select date time
SELECT DISTINCT
Site = 'WEST',
Campus =
CASE
when loc.ParentGUID = 2000001000061001 then 'Allen Hospital'
when loc.ParentGUID = 4000001145061001 then 'Milstein Hospital'
when loc.ParentGUID = 8000001069061001 then 'MS CHONY'
END,
cd.GUID as ClientDocGUID,
cv.GUID AS ClientVisitGUID,
cd.PatCareDocGUID ,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
CASE WHEN ISDATE (rtrim(convert(char(2),c.BirthMonthNum))+'/'+ rtrim(convert(char(2),c.BirthDayNum))+'/'+
rtrim(convert(char(4),c.BirthYearNum))) =1 THEN
convert(char(2),c.BirthMonthNum) +'/'+ rtrim(convert(char(2),c.BirthDayNum))+'/'+
convert(char(4),c.BirthYearNum) ELSE null end DOB,
c.gendercode AS GENDER,
c.languagecode AS LANGUAGECDE,
cv.AdmitDtm AS ADMIT_DATE,
cv.DischargeDtm AS DISCHARGE_DATE,
Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,obserdoc.RecordedDtm),
CV.CurrentLocation AS LOC_TIME_RPT,
LOC.NAME AS LOC_NAME,
obsCatalog.Name as cat_name,
ISNULL(obser.ValueText, obsFS.Value) AS Value_TEXT,
dbo.CV3Service.Description as Prim_diag_description,
dbo.CV3User.displayname as Username,
dbo.CV3User.OccupationCode as User_occupation

INTO #RESULT
FROM dbo.CV3ClientVisit AS cv INNER JOIN
dbo.CV3Location AS loc ON cv.CurrentLocationGUID = loc.GUID INNER JOIN
dbo.CV3ClientDocument AS cd ON cv.GUID = cd.ClientVisitGUID INNER JOIN
dbo.CV3ObservationDocument AS obserdoc ON cd.GUID = obserdoc.OwnerGUID INNER JOIN
dbo.CV3Observation AS obser ON obser.GUID = obserdoc.ObservationGUID INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem ON obscatitem.GUID = obser.ObsItemGUID INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog ON obsCatalog.GUID = obscatitem.MasterObsGUID INNER JOIN
dbo.CV3Client as c ON cv.ClientGUID = c.GUID INNER JOIN
dbo.CV3Service ON cv.ServiceGUID = dbo.CV3Service.GUID INNER JOIN
dbo.CV3User ON cd.UserGUID = dbo.CV3User.GUID LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID
WHERE (cd.PatCareDocGUID IN ('494102020', '470102020'))
--AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
AND cv.TypeCode = 'Inpatient' and cv.idcode ='569 66 04'
--AND cd.AuthoredDtm BETWEEN @start_date AND @end_date

ORDER BY cv.IDCode

--Add all data to the final table
INSERT #Final
( Site ,
Campus,
ClientDocGUID,
ClientVisitGUID,
PatCareDocGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
cat_name,
--Value_TEXT,
Prim_diag_description,
Username,
User_occupation
)
SELECT
Site ,
Campus,
ClientDocGUID,
ClientVisitGUID,
PatCareDocGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
cat_name,
--Value_TEXT,
Prim_diag_description,
Username,
User_occupation
FROM #Result

-- Update final table to assign Date and Time for Result Received and Result Communicated (Notification)for each patient
UPDATE #Final
SET INIT_DATE_OF_RQST = R.value_text
FROM #Result r, #Final f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID = r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_ReqDate_FT'

UPDATE #FINAL
SET INIT_NOTE_DATE = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Date_ft'

UPDATE #FINAL
SET INIT_NOTE_TIME = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Time_ft'

UPDATE #FINAL
SET REFERRING_PROVIDER = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_RefMD_ft'

UPDATE #FINAL
SET REFERRING_SERVICE = R.Value_text
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_RefSvc_FT'

UPDATE #FINAL
SET INIT_SUPPORT_COUNSELING = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Supportive counseling'

UPDATE #FINAL
SET INIT_FAMILY_SUPPORT = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Family support'

UPDATE #FINAL
SET INIT_HOSPTICE_REF_DISCUSS = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Hospice Referral Discussion'

UPDATE #FINAL
SET INIT_PROGNOSTICATION= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Prognostication'

UPDATE #FINAL
SET INIT_PAIN_MGMT= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Pain management'

UPDATE #FINAL
SET INIT_SYMPTOM_MGMT = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Symptom management'

UPDATE #FINAL
SET INIT_WITHHOLD_SUSTAIN= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Withholding or withdrawing of life sustaining treatments'

UPDATE #FINAL
SET INIT_DISCHARGE_PLAN = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Discharge planning'

UPDATE #FINAL
SET INIT_GOALS_CARE = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Goals of care discussion'

UPDATE #FINAL
SET INIT_ADVANCE_DIRECTIVE = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason' AND r.Value_text='Advance Directive discussion:'

UPDATE #FINAL
SET INIT_PATIENT_SUPPORT= 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason1.1' AND r.Value_text='Patient Support:'

UPDATE #FINAL
SET INIT_OTHER = 'YES'
FROM #Result r, #FINAL f
WHERE f.ClientDocGUID = r.ClientDocGUID
and f.ClientVisitGUID=r.ClientVisitGUID
AND r.cat_name = 'PalCare_InitCons_Reason1.2' AND r.Value_text='Other'

DELETE FROM #FINAL where INIT_DATE_OF_RQST IS NULL and INIT_NOTE_DATE IS NULL
AND INIT_NOTE_TIME IS NULL AND REFERRING_PROVIDER IS NULL AND REFERRING_SERVICE IS NULL
AND INIT_SUPPORT_COUNSELING IS NULL AND INIT_FAMILY_SUPPORT IS NULL AND INIT_HOSPTICE_REF_DISCUSS IS NULL
AND INIT_PROGNOSTICATION IS NULL AND INIT_PAIN_MGMT IS NULL AND INIT_SYMPTOM_MGMT IS NULL
AND INIT_WITHHOLD_SUSTAIN IS NULL AND INIT_DISCHARGE_PLAN IS NULL AND INIT_GOALS_CARE IS NULL
AND INIT_ADVANCE_DIRECTIVE IS NULL AND INIT_PATIENT_SUPPORT IS NULL AND INIT_OTHER IS NULL
--Final select
CREATE TABLE #FINAL_2
(
Site varchar(10),
Campus varchar(30),
ClientDocGUID numeric(16,0),
ClientVisitGUID numeric(16,0),
PatCareDocGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
DOB DATETIME,
GENDER VARCHAR(15),
LANGUAGECDE varchar(20),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
Unit_Note_Written VARCHAR(30),
LOC_time_rpt varchar(30),
LOC_NAME VARCHAR(60),
cat_name varchar(60),
--Value_text VARCHAR(2000),
Prim_diag_description VARCHAR(60),
Username varchar(50),
User_Occupation varchar(30),
INIT_DATE_OF_RQST varchar(255),
INIT_NOTE_DATE varchar(255),
INIT_NOTE_TIME varchar(255),
REFERRING_PROVIDER varchar(255),
REFERRING_SERVICE varchar(2000),
INIT_SUPPORT_COUNSELING varchar(2000),
INIT_FAMILY_SUPPORT VARCHAR(2000),
INIT_HOSPTICE_REF_DISCUSS VARCHAR(2000),
INIT_PROGNOSTICATION VARCHAR(2000),
INIT_PAIN_MGMT VARCHAR(2000),
INIT_SYMPTOM_MGMT VARCHAR(2000),
INIT_WITHHOLD_SUSTAIN VARCHAR(2000),
INIT_DISCHARGE_PLAN VARCHAR(2000),
INIT_GOALS_CARE VARCHAR(2000),
INIT_ADVANCE_DIRECTIVE VARCHAR(2000),
INIT_PATIENT_SUPPORT VARCHAR(2000),
INIT_OTHER VARCHAR(2000),
FLUP_PAIN_MGMT VARCHAR(2000),
FLUP_SYMPTOM_MGMT VARCHAR(2000),
FLUP_DECISION_MAKING VARCHAR(2000),
FLUP_GOALS_CARE VARCHAR(2000),
FLUP_PAT_FAM_SUP VARCHAR(2000),
FLUP_PROGNOSIS VARCHAR(2000),
FLUP_WITHHOLD_SUSTAIN VARCHAR(2000),
FLUP_DISCHARGE_PLAN VARCHAR(2000),
FLUP_HLTH_CARE_AGENT VARCHAR(2000),
FLUP_ADVANCE_DIRECTIVE VARCHAR(2000),
FLUP_HOSPTICE_REF_DISCUSS VARCHAR(2000),
FLUP_EDU_SUP_STAFF VARCHAR(2000),
FLUP_FAM_CONF VARCHAR(2000),
FLUP_CODE_STATUS VARCHAR(2000),
FLUP_HOSPTICE_REF VARCHAR(2000))

SELECT DISTINCT
Site ,
Campus,
ClientDocGUID,
ClientVisitGUID,
PatCareDocGUID,
MRN,
EncNumber,
PATIENT,
DOB,
GENDER,
LANGUAGECDE,
ADMIT_DATE,
DISCHARGE_DATE,
Unit_Note_Written,
LOC_TIME_RPT,
LOC_NAME,
Prim_diag_description,
Username,
User_occupation,
INIT_DATE_OF_RQST ,
INIT_NOTE_DATE,
INIT_NOTE_TIME,
REFERRING_PROVIDER ,
REFERRING_SERVICE ,
INIT_SUPPORT_COUNSELING,
INIT_FAMILY_SUPPORT,
INIT_HOSPTICE_REF_DISCUSS ,
INIT_PROGNOSTICATION,
INIT_PAIN_MGMT,
INIT_SYMPTOM_MGMT,
INIT_WITHHOLD_SUSTAIN,
INIT_DISCHARGE_PLAN,
INIT_GOALS_CARE,
INIT_ADVANCE_DIRECTIVE,
INIT_PATIENT_SUPPORT,
INIT_OTHER

INTO #FINAL_2

FROM #Final
ORDER BY MRN

UPDATE #FINAL_2
SET FLUP_PAIN_MGMT = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
AND F.cat_name = 'PalCare_InitCons_PC_PainMgmt_ft'

UPDATE #FINAL_2
SET FLUP_SYMPTOM_MGMT = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_SympMgmt_ft'

UPDATE #FINAL_2
SET FLUP_DECISION_MAKING = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_Decision_ft'

UPDATE #FINAL_2
SET FLUP_GOALS_CARE= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_Goals_ft'

UPDATE #FINAL_2
SET FLUP_PAT_FAM_SUP= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_PtFam_ft'

UPDATE #FINAL_2
SET FLUP_PROGNOSIS= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_Prog_ft'

UPDATE #FINAL_2
SET FLUP_WITHHOLD_SUSTAIN= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_Interven_ft'

UPDATE #FINAL_2
SET FLUP_DISCHARGE_PLAN = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_DC_ft'

UPDATE #FINAL_2
SET FLUP_HLTH_CARE_AGENT= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_HCagent_ft'

UPDATE #FINAL_2
SET FLUP_ADVANCE_DIRECTIVE = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_advdirgen_ft'

UPDATE #FINAL_2
SET FLUP_HOSPTICE_REF_DISCUSS= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_hosprefdis_ft'

UPDATE #FINAL_2
SET FLUP_EDU_SUP_STAFF = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_edu_ft'

UPDATE #FINAL_2
SET FLUP_FAM_CONF= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_famconf_ft'

UPDATE #FINAL_2
SET FLUP_CODE_STATUS = 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_codeclar_ft'

UPDATE #FINAL_2
SET FLUP_HOSPTICE_REF= 'YES'
FROM #FINAL f, #FINAL_2 fn
WHERE f.ClientDocGUID = fn.ClientDocGUID
and f.ClientVisitGUID=F.CLIENTVISITGUID
AND F.CAT_NAME = 'PalCare_InitCons_PC_Hospice_ft'

--DROP TABLE #Result
--DROP TABLE #Final
DROP TABLE #FINAL_2

END

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 09:29:20

Your error doesn't make sense based on your code. One thing you are doing wrong is creating the #final_2 table and then selecting into it, which tries to create the table again

CREATE TABLE #final_2 (<stuff>)

INSERT INTO #Final_2
SELECT <stuff>
FROM #Final

Jim

Everyday I learn something that somebody else already knew

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-15 : 10:31:50

thank you, funny how placement of statements makes a huge difference.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-15 : 10:40:34

It's not just the placement, they're two different things. Select <stuff> INTO aTable FROM anotherTable tells sql to create the aTable table. INSERT INTO aTable. Select tells SQL to use the aTable that already exists. Creatunt the table explicitly like you did is the right way to do it.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources