SQLTeam.com | Weblogs | Forums

Insert data into existing table


#1

The following SP I want to use in a job to insert yesterdays records into a table.

I am having an issue with this. Can anyone advise what I am doing incorrectly?

INSERT INTO DBO.CHARGECAPTURE

CREATE TABLE #FirstNeonate
(
SessionID int,
DeliveryTime datetime,
PRIMARY KEY (SessionID)
)

INSERT INTO #FirstNeonate
SELECT DISTINCT
F.Sessionid,
F.Valuetime AS DeliveryTime
FROM datamartdb2.dbo.BVFIndings AS F
INNER JOIN datamartdb2.dbo.BLSession_Extended AS S
ON S.Sessionid = F.Sessionid
WHERE
F.Atomid = 24130
and F.Instance = char(127)+'34323[1]'
and F.valuestr = 'true'
--and ( s.facilityid = @FacilityID )
AND (
(
F.EntryTime between DATEADD(day, -1, DATEADD(day, DATEDIFF(day,'19000101', GETDATE()),'19000101'))
and DATEADD(day, DATEDIFF(day,'19000101', GETDATE()),'19000101')

              --between '08/01/2016' and getdate()--@StartTimeOut and @EndTimeOut
        )
          )

--####################################################################################################

CREATE TABLE #TMP_AllNeonates
(
SessionID int,
Instance varchar(255),
MultiCardNumber INT,
[MRN] varchar(255),
DeliveryTime datetime,
entrytime datetime,
[LastName] varchar(255),
[FirstName] varchar(255),
[FacilityName] varchar(255),
[FacilityID] int,
DeliveryTimeOfFirstNeonate DateTime/,
PRIMARY KEY (SessionID, Instance)
/
)

INSERT INTO #TMP_AllNeonates
SELECT DISTINCT
FirstNeonate.Sessionid,
F.Instance,
F.MultiCardNumber,
S.MRN,
F.ValueTime AS DeliveryTime,
f.EntryTime,
S.LastName,
S.FirstName,
S.Facility_Name AS [FacilityName],
S.FacilityID AS [FacilityID],
FirstNeonate.DeliveryTime AS DeliveryTimeOfFirstNeonate
FROM #FirstNeonate AS FirstNeonate
INNER JOIN datamartdb2.dbo.BVFIndings AS F
ON FirstNeonate.Sessionid = F.Sessionid
JOIN datamartdb2.dbo.BLSession_Extended AS S
ON S.Sessionid = F.Sessionid
WHERE F.ObjectName = 'Time_Of_Delivery!Delivery_Report_Neonate[#]'
AND F.valuestr = 'true'
--since we might have duplicates in the finding
and entrytime = (select max(entrytime) from datamartdb2.dbo.BVFIndings F1
where F1.sessionid = f.sessionid
and F1.AtomID = F.AtomID
And F1.Instance = F.Instance
)
--####################################################################################################
SELECT
F.SessionID,
F.Instance,
F.MultiCardNumber AS [Neonate#],
(SELECT MAX(MultiCardNumber)
FROM #TMP_AllNeonates F1
WHERE F1.sessionid = F.sessionid
) AS [Total Neonates],
F.LastName AS [Last Name],
F.FirstName AS [First Name],
F.MRN AS [MRN],
dbo.EFgetAccountNum(F.Sessionid) AS [Account],
F.DeliveryTime AS [Delivery Date],
dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) AS [Delivery Type],
CASE
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('General_Anesthesia!Cesarean_Section','General_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'General'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Epidural_Anesthesia!Cesarean_Section','Epidural_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Spinal_Anesthesia!Cesarean_Section','Spinal_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('CLE_Anesthesia!Cesarean_Section','CLE_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Combined_Spinal_Epidural_Anesth!Cesarean_Section','Combined_Spinal_Epidural_Anesth!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Regional'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Pudendal_Block!Cesarean_Section','Pudendal_Block!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Pudendal'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Local_Anesthesia!Cesarean_Section','Local_Anesthesia!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'Local'
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Anesthesia_None!Cesarean_Section','Anesthesia_None!Normal_Vaginal_Delivery',F.Sessionid,'true')='true'
THEN 'None'
WHEN dbo.EFgetFindingValue (1959, '15216[1]', F.sessionid) = 'true' -- Spinal_Anesthesia!General_Operative_Procedure[#]
THEN 'Regional'
WHEN dbo.EFgetFindingValue (1958, '15216[1]', F.sessionid) = 'true' -- Epidural_Anesthesia!General_Operative_Procedure[#]
THEN 'Regional'
WHEN dbo.EFgetFindingValue (31134, '15216[1]', F.sessionid) = 'true' -- Combined_Spinal_Epidural_Anesth!General_Operative_Procedure[#]
THEN 'Regional'
WHEN dbo.EFgetFindingValue (8077, '15216[1]', F.sessionid) = 'true' -- General_Anesthesia!General_Operative_Procedure[#]
THEN 'General'
ELSE ''

END AS [Anesthesia],
CASE
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('General_Anesthesia!Normal_Vaginal_Delivery','General_Anesthesia!Cesarean_Section',F.Sessionid,'true') = 'true'
THEN '65000011'
WHEN EXISTS (select top 1 1
from datamartdb2.dbo.BVFindings f1
where f1.sessionId = f.sessionId
and f1.AtomID in (1958,1959,31134,38170)
and f1.Instance in (CHAR(127)+'5995',CHAR(127)+'1996')
and f1.ValueStr = 'true'
)
THEN '65000010'
END AS [Anesth. Code],
replace(replace(dbo.CRGetEvent(F.sessionid,F.Instance,'Maternal Complications'),'Tubal Ligation ; ',''),'Tubal Ligation','')
AS [Complications], --
dbo.CRGetEvent(F.Sessionid,F.Instance,'Lacerations') AS [Lacerations],
CASE
WHEN dbo.EFgetFindingValueByValueSTR_2Objects('Entry_Time_To_OR!Cesarean_Section','Time_Out_Of_OR!Cesarean_Section',F.Sessionid,'true') = 'true'
THEN '67000553'

END AS [PACU Code],
CASE WHEN
(Select top 1 valuestr
From BVfindings F2
WHERE F2.Sessionid = F.Sessionid
AND F2.Objectname like 'Entry_Time_To_OR!%'
AND F2.Valuestr = 'true') = 'true'
THEN '67000553'
ELSE ''
END AS PACU2,
CASE
WHEN (SELECT MAX(MultiCardNumber)
FROM #TMP_AllNeonates F1
WHERE F1.sessionid = F.sessionid
) >= 2
THEN '67000138'
WHEN dbo.EFgetFindingValue(2069,F.Instance,F.SessionID) = 'true' --Vacuum
THEN '67000135'
WHEN dbo.EFgetFindingValue(2070,F.Instance,F.SessionID) = 'true' --Forceps
THEN '67000135'
WHEN (select top 1 f1.valuestr
from datamartdb2.dbo.BVFindings f1
where f1.sessionId = F.sessionid
and f1.ObjectName in ('Placental_Manual_Lysis!Delivery_Report_Neonate[#]',
'Placental_Manual_Lysis!Birth_Canal_Revision',
'Retained_Placenta_P!Delivery_Report_Neonate[#]',
'Retained_Placenta_P!Birth_Canal_Revision',
'Pelvic_Hematoma_P!Birth_Canal_Revision',
'Postpartum_Hemorrhage!Normal_Vaginal_Delivery',
'Postpartum_Hemorrhage!Birth_Canal_Revision'
)
and dbo.EFgetFindingValue(1996,F.Instance,F.sessionid) is null --no CS
) = 'true'
THEN '67000135'
WHEN dbo.EFgetFindingValueByObjectName('Perineal_Laceration_Degree!Normal_Vaginal_Delivery',F.Sessionid) in ('III','IV')
THEN '67000135'
WHEN dbo.EFgetFindingValueByObjectName('Vaginal_Laceration_Degree!Normal_Vaginal_Delivery',F.Sessionid) in ('III','IV')
THEN '67000135'
WHEN dbo.EFgetFindingValue(1996,F.Instance,F.sessionid) = 'true' --CS
THEN '67000137'
ELSE '67000136'
END AS [CDM],
CASE WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%' --no CS
THEN 'Tubal Ligation after VD'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
THEN 'CS Tubal Ligation'

   WHEN EXISTS (select top 1 1
                       from BVFindings f1
                       where f1.sessionId = f.sessionId
                       and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Total_Abdominal_Hysterectomy',
                                                                 'Supracervical_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
                                                                 'Supracervical_Hysterectomy!Discharge',
                                                                 'SP_Hysterectomy',
                                                                 'Hysterectomy!G[#]',
                                                                 'Hysterectomy!Discharge',
                                                                 'Hysterectomy!Cesarean_Section',
                                                                 'Hysterectomy',
                                                                 'Cesarean_Section!Emergency[Hysterectomy]',
                                                                 'Cesarean_Hysterectomy!Discharge')
                       and f1.ValueStr = 'true')
   AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
   THEN 'CS Hysterectomy'
   WHEN EXISTS (select top 1 1
                       from BVFindings f1
                       where f1.sessionId = f.sessionId
                       and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Total_Abdominal_Hysterectomy',
                                                                 'Supracervical_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
                                                                 'Supracervical_Hysterectomy!Discharge',
                                                                 'SP_Hysterectomy',
                                                                 'Hysterectomy!G[#]',
                                                                 'Hysterectomy!Discharge',
                                                                 'Hysterectomy!Cesarean_Section',
                                                                 'Hysterectomy',
                                                                 'Cesarean_Section!Emergency[Hysterectomy]',
                                                                 'Cesarean_Hysterectomy!Discharge')
                       and f1.ValueStr = 'true')
   AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%'
   THEN 'Hysterectomy after VD'

   WHEN
   (SELECT MAX (A.Text_Str)
   FROM dbo.BLFollowupLog    A
   WHERE A.sessionid = F.sessionid
   AND A.Text_Str LIKE '%iud insertion%')LIKE '%iud insertion%'
   THEN 'IUD_Insertion'
    WHEN EXISTS (select top 1 1
                    from BVFindings f1
                    where f1.sessionId = f.sessionId
                    and F1.Objectname IN ('Levonorgestrel','IUD_Insertion','Copper')
                    and f1.ValueStr = 'true'
                    )
   THEN 'IUD_Insertion'
   WHEN EXISTS (select top 1 1
                    from BVFindings f1
                    where f1.sessionId = f.sessionId
                    and F1.Objectname = 'Etonogestrel'
                    and f1.ValueStr = 'true'
                    )
   THEN 'Implant'
   ELSE ''

END AS [Procedure],

CASE WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%' --no CS
THEN '67000147'
WHEN EXISTS (select top 1 1
from BVFindings f1
where f1.sessionId = f.sessionId
and F1.Objectname IN ('Tubal_Ligation!Recovery_Report_T[#]','Tubal_Ligation!General_Operative_Procedure[#]',
'Tubal_Ligation!Discharge', 'Tubal_Ligation!Cesarean_Section', 'Surgeon_Present!Tubal_Ligation')
and f1.ValueStr = 'true')
AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
THEN '67000148'

     WHEN EXISTS (select top 1 1
                       from BVFindings f1
                       where f1.sessionId = f.sessionId
                       and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Total_Abdominal_Hysterectomy',
                                                                 'Supracervical_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
                                                                 'Supracervical_Hysterectomy!Discharge',
                                                                 'SP_Hysterectomy',
                                                                 'Hysterectomy!G[#]',
                                                                 'Hysterectomy!Discharge',
                                                                 'Hysterectomy!Cesarean_Section',
                                                                 'Hysterectomy',
                                                                 'Cesarean_Section!Emergency[Hysterectomy]',
                                                                 'Cesarean_Hysterectomy!Discharge')
                       and f1.ValueStr = 'true')
   AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) LIKE 'Cesarean%'
   THEN '67000150'
   WHEN EXISTS (select top 1 1
                       from BVFindings f1
                       where f1.sessionId = f.sessionId
                       and F1.Objectname IN ('Total_Abdominal_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Total_Abdominal_Hysterectomy',
                                                                 'Supracervical_Hysterectomy!Recovery_Report_T[#]',
                                                                 'Supracervical_Hysterectomy!General_Operative_Procedure[#]',
                                                                 'Supracervical_Hysterectomy!Discharge',
                                                                 'SP_Hysterectomy',
                                                                 'Hysterectomy!G[#]',
                                                                 'Hysterectomy!Discharge',
                                                                 'Hysterectomy!Cesarean_Section',
                                                                 'Hysterectomy',
                                                                 'Cesarean_Section!Emergency[Hysterectomy]',
                                                                 'Cesarean_Hysterectomy!Discharge')
                       and f1.ValueStr = 'true')
   AND dbo.CRChargeCaptureTypeOfDelivery(F.Sessionid,F.Instance) NOT LIKE 'Cesarean%'
   THEN '67000151'

   WHEN
          (SELECT MAX (A.Text_Str)
          FROM dbo.BLFollowupLog    A
          WHERE A.sessionid = F.sessionid
          AND A.Text_Str LIKE '%iud insertion%')LIKE '%iud insertion%'
          THEN '65000139'
          WHEN EXISTS (select top 1 1
                                            from BVFindings f1
                                            where f1.sessionId = f.sessionId
                                            and F1.Objectname IN ('Levonorgestrel','IUD_Insertion','Copper')
                                            and f1.ValueStr = 'true'
                                            )
          THEN '65000139'
          WHEN EXISTS (select top 1 1
                                            from BVFindings f1
                                            where f1.sessionId = f.sessionId
                                            and F1.Objectname = 'Etonogestrel'
                                            and f1.ValueStr = 'true'
                                            )
          THEN '65000140'
   ELSE ''

END AS 'Proc_Charge',

CASE
WHEN dbo.EFgetFindingValue(213693, '1996', F.Sessionid) = 'true'
THEN 'yes'
ELSE ''
END AS 'Adhesive Barrier',
ISNULL (dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid), '') AS Manufacturer,
ISNULL (dbo.EFgetFindingValue(135321,'213694[1996]',F.Sessionid), '') AS CatalogNumber,
ISNULL (dbo.EFgetFindingValue(68613,'213694[1996]',F.Sessionid), '') AS LotNumber,
ISNULL (dbo.EFgetFindingValue(73736,'213694[1996]',F.Sessionid), '') AS Qty,
CASE
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE '%inter%'
THEN '67000635'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'sepra%'
THEN '67000629'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'surgi%'
THEN '67000603'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'bakri%'
THEN '67000608'
WHEN dbo.EFgetFindingValue(156991,'213694[1996]',F.Sessionid) LIKE 'floseal%'
THEN '67000634'
ELSE ''
END AS 'Implant Charge',

f.entrytime ,
F.FacilityName AS [FacilityName],
F.[FacilityID] AS [FacilityID],
F.DeliveryTimeOfFirstNeonate

FROM
#TMP_AllNeonates AS F

SELECT * FROM DBO.CHARGECAPTURE ORDER BY [Delivery Date] DESC

--DROP TABLE DBO.CHARGECAPTURE

drop table #FirstNeonate
drop table #TMP_AllNeonates


#2

You didn't tell us what the issue is.


#3

The following SP I want to use in a job to insert yesterdays records into a table.

how do i insert the data into an existing table using the sp below.
it is not working.


#4

You haven't provided enough info for us to help. Are you getting an error? Are you getting incorrect results? Explain exactly what "it is not working" means.

What you have posted is an incomplete stored procedure. If the stored procedure gives you yesterday's data already, then just use INSERT INTO ... EXEC storedproc...