• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 136
  • Last Modified:

Output query to a text file

Hello,

I am trying to do something but I am not sure how to append this to the query.  I have a query that will get the patient demographic information that I need then on the next line of the file I need to add the diagnosis which could be one line or 30.  Once I have all of the diagnosis finished I need to move to the next patient.

Here is a very basic example.
TEST, PATIENT|123456789|1234567890|2016-10-11 13:05:25.000<crlf>
3|146523.25648 <crlf>
3|15648254.21 <crlf>
3|148754263.33 <crlf>
TEST, PATIENT2|145236987|1236549870|2016-10-10 15:42:35.000<crlf>
3|1546287.23 <crlf>
3|1524684.2563 <crlf>
3|16589640.285 <crlf>
3|165482.547 <crlf>

The lines with the patient name is from one query and the diagnosis is from another query.

Any help on how I might sent this up?

Thanks,
Rodger
0
Rodger
Asked:
Rodger
  • 14
  • 10
  • 4
1 Solution
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Here's a classical way to do this (I will continue thinking about more efficient ways, but  this  is the knee-jerk, SQL 2000 style way that is proven to work)

USE tempdb;
GO
SET NOCOUNT ON;

DECLARE @patientData TABLE (PatientId              INT          NOT NULL,
                            preAppendedPatientData VARCHAR(MAX) NOT NULL,
                            isAppendComplete       BIT          DEFAULT(0)
                           );

DECLARE @diagnosisData TABLE (PatientId              INT          NOT NULL,
                              Diagnosis              VARCHAR(MAX) NOT NULL,
                              isAppendComplete       BIT          DEFAULT(0)
                             );

INSERT INTO @patientData (PatientId, preAppendedPatientData)
VALUES (1, 'TEST, PATIENT|123456789|1234567890|2016-10-11 13:05:25.000'),
       (2, 'TEST, PATIENT2|145236987|1236549870|2016-10-10 15:42:35.000');

INSERT INTO @diagnosisData (PatientId, Diagnosis)
VALUES (1, '3|146523.25648'),
       (1, '3|15648254.21'),
       (1, '3|148754263.33'),
       (2, '3|1546287.23'),
       (2, '3|1524684.2563'),
       (2, '3|16589640.285'),
       (2, '3|165482.547');

--Simple, classical way
DECLARE @resultsString VARCHAR(MAX)      = NULL;
DECLARE @processingPatientId INT         = NULL;
DECLARE @intermediateString VARCHAR(MAX) = NULL;

WHILE EXISTS (SELECT pd.PatientId
              FROM @patientData AS pd
              WHERE pd.isAppendComplete = 0
             )
BEGIN
    SELECT TOP 1 @processingPatientId = pd.PatientId,
                 @resultsString = ISNULL(@resultsString,'') + (pd.preAppendedPatientData + CHAR(13) + CHAR(10))
    FROM @patientData AS pd
    WHERE pd.isAppendComplete = 0;

    SELECT @intermediateString = COALESCE(@intermediateString + CHAR(13) + CHAR(10) ,'') + dd.Diagnosis
    FROM @diagnosisData AS dd
    WHERE dd.PatientId = @processingPatientId;

    SELECT @resultsString = @resultsString + @intermediateString + CHAR(13) + CHAR(10);

    --Important, required - Mark patient record as processed
    UPDATE pd
    SET pd.isAppendComplete = 1
    FROM @patientData AS pd
    WHERE pd.PatientId = @processingPatientId;

    --Important, required
    SET @intermediateString = NULL;
END

SELECT @resultsString AS ClassicalWay;

/*************************************
RESULTS 
--------------------------------------
ClassicalWay
------------------------------------------------------------
TEST, PATIENT|123456789|1234567890|2016-10-11 13:05:25.000
3|146523.25648
3|15648254.21
3|148754263.33
TEST, PATIENT2|145236987|1236549870|2016-10-10 15:42:35.000
3|1546287.23
3|1524684.2563
3|16589640.285
3|165482.547
*************************************/

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
For the same test data as above, here's the "new" XML way:

--New Way
DECLARE @patientDataXML XML          = NULL;
DECLARE @resultsString  VARCHAR(MAX) = NULL;

SELECT @patientDataXML = (SELECT pd.PatientId, 
                                 pd.preAppendedPatientData, 
                                 dd.Diagnosis, 
                                 DENSE_RANK() OVER (ORDER BY pd.PatientId) AS SequenceNumber
                          FROM @patientData AS pd
                          INNER JOIN @diagnosisData AS dd ON pd.PatientId = dd.PatientId
                          FOR XML AUTO
                         );

;WITH OrderedData (Data, SequenceNumber)
AS (
    SELECT T.c.value('@preAppendedPatientData[1]','VARCHAR(MAX)') AS [Data],
           T.c.value('@PatientId[1]','INT') AS [SequenceNumber]
    FROM @patientDataXML.nodes('pd') T(c)
    UNION
    SELECT T.c.value('@Diagnosis[1]','VARCHAR(MAX)') AS [Data],
           T.c.value('@SequenceNumber[1]','INT') AS [SequenceNumber]
    FROM @patientDataXML.nodes('pd/dd') T(c)
   )
SELECT @resultsString = COALESCE(@resultsString + CHAR(13) + CHAR(10) ,'') + [od].[Data]
FROM OrderedData AS od
ORDER BY [od].[SequenceNumber] ASC, [od].[Data] DESC;

SELECT @resultsString AS [XMLWay];

Open in new window

0
 
RodgerSystems AnalystAuthor Commented:
Nakul.

Thank you very much.  I will be working on this today and will let you know how it works out.  The way the vendor wants the output it will be the classic way, but having the XML version is nice as well.

Thanks again,
Rodger
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RodgerSystems AnalystAuthor Commented:
Nakul,

I created the temp tables, but when I am trying to insert the formated data into the table I am getting an error.

Here is the syntax
Declare @patientData TABLE  (PatientID                  INT               NOT NULL,
                                          preAppendedPatientData      VARCHAR(MAX) NOT NULL,
                                          isAppendedComplete          BIT                   DEFAULT(0)
                                          );

INSERT INTO @patientData (PatientID, preAppendedPatientData)
SELECT (t2.EOC_, t2.RecordType_ + '|' + t2.EOC_ + '|' + t2.PatientID_  + '|' + t2.LastName_ + '|' + t2.FirstName_ + '|' + t2.BirthDate_ + '|' + t2.SexOnArrival_ + '|' + t2.Race_ + '|' + t2.Ethnicity_ + '|' + t2.PreferredLanguage_ + '|' + t2.PostalCode_ + '|' + t2.PaymentSourcePrimary_ + '|' + t2.PaymentSourceSecondary_ + '|' + t2.PaymentSourceSecondary2_ + '|' + t2.EDEncounter_ + '|' + t2.InpatientEncounter_ + '|' + t2.RecordType2_ + '|' + t2.ArrivalDateTime_ + '|' + t2.AdmitToHospitalInpatient_ + '|' + t2.OrderAdmitDateTime_ + '|' + t2.EDDepartureDateTime_ + '|' + t2.AdmitDateTime_ + '|' + t2.DischargeDateTime_ + '|' + t2.AdmissionSource_ + '|' + t2.AdmissionType_ + '|' + t2.DischargeStatusDisposition_ + '|' + t2.LastKnownWellDateTime_ + '|' + t2.RoomNumber_ + '|' + t2.NonICULocation_ + '|' + t2.ICULocation_ + '|' + t2.ICUAdmissionOrTransfer_ + '|' + t2.ICUAdmitDateTime_ + '|' + t2.ICUTransferDateTime_ + '|' + t2.ICUArrivalDateTime_ + '|' + t2.ICUDischargeDateTime_ + '|' + t2.TransferFromLocation_ + '|' + t2.TransferFromLocationDateTime_ + '|' + t2.TransferToLocation_ + '|' + t2.TransferToLocationDateTime_ + '|' + t2.FillerField1_ + '|' + t2.FillerField2_ + '|' + t2.TimeOfDeath_ + '|' + t2.PreliminaryCauseOfDeath_ + '|' + t2.ClinicalTrial_ + '|' + t2.ClinicalTrialStartDateTime_ + '|' + t2.ClinicalTrialEndDateTime_ + '|' + t2.MedicareHICNumber_ )
FROM @Temp2 AS t2

Here is the error.
Msg 102, Level 15, State 1, Line 399
Incorrect syntax near ','.

The error is referencing the comma on the SELECT statement EOC,

Thanks again,
Rodger
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The round brackets are not required when you are selecting data. They are required when using row constructors (Values clause as shown in my example).

So in your case, it should be...

DECLARE @patientData TABLE  (PatientID               INT          NOT NULL,
                             preAppendedPatientData  VARCHAR(MAX) NOT NULL,
                             isAppendedComplete      BIT          DEFAULT(0)
                            );

INSERT INTO @patientData (PatientID, preAppendedPatientData)
SELECT t2.EOC_, t2.RecordType_ + '|' + t2.EOC_ + '|' + t2.PatientID_  + '|' + t2.LastName_ + '|' + t2.FirstName_ + '|' + t2.BirthDate_ + '|' + t2.SexOnArrival_ + '|' + t2.Race_ + '|' + t2.Ethnicity_ + '|' + t2.PreferredLanguage_ + '|' + t2.PostalCode_ + '|' + t2.PaymentSourcePrimary_ + '|' + t2.PaymentSourceSecondary_ + '|' + t2.PaymentSourceSecondary2_ + '|' + t2.EDEncounter_ + '|' + t2.InpatientEncounter_ + '|' + t2.RecordType2_ + '|' + t2.ArrivalDateTime_ + '|' + t2.AdmitToHospitalInpatient_ + '|' + t2.OrderAdmitDateTime_ + '|' + t2.EDDepartureDateTime_ + '|' + t2.AdmitDateTime_ + '|' + t2.DischargeDateTime_ + '|' + t2.AdmissionSource_ + '|' + t2.AdmissionType_ + '|' + t2.DischargeStatusDisposition_ + '|' + t2.LastKnownWellDateTime_ + '|' + t2.RoomNumber_ + '|' + t2.NonICULocation_ + '|' + t2.ICULocation_ + '|' + t2.ICUAdmissionOrTransfer_ + '|' + t2.ICUAdmitDateTime_ + '|' + t2.ICUTransferDateTime_ + '|' + t2.ICUArrivalDateTime_ + '|' + t2.ICUDischargeDateTime_ + '|' + t2.TransferFromLocation_ + '|' + t2.TransferFromLocationDateTime_ + '|' + t2.TransferToLocation_ + '|' + t2.TransferToLocationDateTime_ + '|' + t2.FillerField1_ + '|' + t2.FillerField2_ + '|' + t2.TimeOfDeath_ + '|' + t2.PreliminaryCauseOfDeath_ + '|' + t2.ClinicalTrial_ + '|' + t2.ClinicalTrialStartDateTime_ + '|' + t2.ClinicalTrialEndDateTime_ + '|' + t2.MedicareHICNumber_ 
FROM @Temp2 AS t2

Open in new window

1
 
RodgerSystems AnalystAuthor Commented:
Nakul,

I am not sure what has happened but the query has been running for over 18 hours.  I must be stuck in some loop.  I tried to attach my code, but it does not see to be working.

Thanks again,
Rodger
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Can you share the loop that you implemented?

Note the following section in my code where I update the isAppendComplete bit in the WHILE loop (lines #49 to 56)...If this is not done, you keep processing the same record again and again, i.e. in an infinite loop.

    --Important, required - Mark patient record as processed
    UPDATE pd
    SET pd.isAppendComplete = 1
    FROM @patientData AS pd
    WHERE pd.PatientId = @processingPatientId;

    --Important, required
    SET @intermediateString = NULL;

Open in new window

0
 
RodgerSystems AnalystAuthor Commented:
Here is the code

DECLARE @patientData TABLE  (PatientID	           	BIGINT	    ,
			       preAppendedPatientData		VARCHAR(MAX),
			       isAppendedComplete	    	BIT DEFAULT(0)
							);

DECLARE @diagnosisData TABLE (PatientID	            	BIGINT	     ,
				Diagnosis            		VARCHAR(MAX) ,
				isAppendedComplete	    	BIT DEFAULT(0)
							;

INSERT INTO	@patientData (PatientID, preAppendedPatientData)
SELECT	t2.EOC_,  '"' + t2.RecordType_ + '"|"' + t2.EOC_ + '"|"' + t2.PatientID_  + '"|"' + t2.LastName_ + '"|"' + t2.FirstName_ + '"|"' + t2.BirthDate_ + '"|"' + t2.SexOnArrival_ + '"|"' + t2.Race_ + '"|"' + t2.Ethnicity_ + '"|"' + t2.PreferredLanguage_ + '"|"' + t2.PostalCode_ + '"|"' + t2.PaymentSourcePrimary_ + '"|"' + t2.PaymentSourceSecondary_ + '"|"' + t2.PaymentSourceSecondary2_ + '"|"' + t2.EDEncounter_ + '"|"' + t2.InpatientEncounter_ + '"|"' + t2.RecordType2_ + '"|"' + t2.ArrivalDateTime_ + '"|"' + t2.AdmitToHospitalInpatient_ + '"|"' + t2.OrderAdmitDateTime_ + '"|"' + t2.EDDepartureDateTime_ + '"|"' + t2.AdmitDateTime_ + '"|"' + t2.DischargeDateTime_ + '"|"' + t2.AdmissionSource_ + '"|"' + t2.AdmissionType_ + '"|"' + t2.DischargeStatusDisposition_ + '"|"' + t2.LastKnownWellDateTime_ + '"|"' + t2.RoomNumber_ + '"|"' + t2.NonICULocation_ + '"|"' + t2.ICULocation_ + '"|"' + t2.ICUAdmissionOrTransfer_ + '"|"' + t2.ICUAdmitDateTime_ + '"|"' + t2.ICUTransferDateTime_ + '"|"' + t2.ICUArrivalDateTime_ + '"|"' + t2.ICUDischargeDateTime_ + '"|"' + t2.TransferFromLocation_ + '"|"' + t2.TransferFromLocationDateTime_ + '"|"' + t2.TransferToLocation_ + '"|"' + t2.TransferToLocationDateTime_ + '"|"' + t2.FillerField1_ + '"|"' + t2.FillerField2_ + '"|"' + t2.TimeOfDeath_ + '"|"' + t2.PreliminaryCauseOfDeath_ + '"|"' + t2.ClinicalTrial_ + '"|"' + t2.ClinicalTrialStartDateTime_ + '"|"' + t2.ClinicalTrialEndDateTime_ + '"|"' + t2.MedicareHICNumber_ + '"'
FROM 		@Temp2 AS t2
WHERE 		t2.EOC_ IS NOT NULL

INSERT INTO  	@diagnosisData (PatientID, Diagnosis)
SELECT 	t3.EOC, '"' + t3.RecordType + '"|"' + t3.EOC + '"|"' + t3.Code + '"|"' + t3.CodeType + '"|"' + t3.Priority + '"|"' + t3.Status + '"|"' + t3.Severity + '"|"' + t3.StartDate + '"|"' + t3.EndDate + '"'
FROM 	@Temp3 AS t3


DECLARE	@resultsString			VARCHAR(MAX)		= NULL;
DECLARE @processingPatientsID	INT					= NULL;
DECLARE @intermediateString		VARCHAR(MAX)		= NULL;

WHILE EXISTS (	SELECT	pd.PatientID
	       	FROM   	@patientData AS pd
		WHERE	pd.isAppendedComplete = 0)

BEGIN
    SELECT TOP 1	@processingPatientsID = pd.PatientId,
	@resultsString = ISNULL(@resultsString,'') + (pd.preAppendedPatientData + CHAR(13) + CHAR(10))
    FROM		@patientData AS pd
    WHERE		pd.isAppendedComplete = 0;

    SELECT	@intermediateString = COALESCE(@intermediateString + CHAR(13) + CHAR(10) ,'') + dd.Diagnosis
    FROM		@diagnosisData AS dd
    WHERE		dd.PatientId = @processingPatientsID;

    SELECT	@resultsString = @resultsString + @intermediateString + CHAR(13) + CHAR(10);

    --IMPORTANT, REQUIRED - MARK PATIENT RECORD AS PROCESSED
    UPDATE			pd
    SET				pd.isAppendedComplete = 1
    FROM			@patientData AS pd
    WHERE			pd.PatientId = @processingPatientsID;

    --IMPORTANT, REQUIRED
    SET				@intermediateString = NULL;
END

SELECT @resultsString AS ClassicalWay;

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
The code looks okay. The only thing that comes to mind is that my sample code uses table variables. Table variables do not have statistics and when working with large amounts of data they can slow down the queries due to inefficient query plans and spilling over into the tempdb.

Can you replace them with temporary tables or physical tables with the necessary indexes and constraints?

IF OBJECT_ID('tempdb..#patientsData','U') IS NOT NULL
    DROP TABLE #patientsData;
GO

IF OBJECT_ID('tempdb..#diagnosisData','U') IS NOT NULL
    DROP TABLE #diagnosisData;
GO

CREATE TABLE #patientData (PatientID                    BIGINT       PRIMARY KEY CLUSTERED,
                           preAppendedPatientData       VARCHAR(MAX) ,
                           isAppendedComplete           BIT DEFAULT(0),
                          );

CREATE TABLE #diagnosisData (DiagnosisId                BIGINT       NOT NULL IDENTITY(1,1),
                             PatientID                  BIGINT       ,
                             Diagnosis                  VARCHAR(MAX) ,
                             isAppendedComplete         BIT DEFAULT(0),
                             PRIMARY KEY CLUSTERED (PatientID, DiagnosisID)
                            );

INSERT INTO #patientData (PatientID, preAppendedPatientData)
SELECT  t2.EOC_,  '"' + t2.RecordType_ + '"|"' + t2.EOC_ + '"|"' + t2.PatientID_  + '"|"' + t2.LastName_ + '"|"' + t2.FirstName_ + '"|"' + t2.BirthDate_ + '"|"' + t2.SexOnArrival_ + '"|"' + t2.Race_ + '"|"' + t2.Ethnicity_ + '"|"' + t2.PreferredLanguage_ + '"|"' + t2.PostalCode_ + '"|"' + t2.PaymentSourcePrimary_ + '"|"' + t2.PaymentSourceSecondary_ + '"|"' + t2.PaymentSourceSecondary2_ + '"|"' + t2.EDEncounter_ + '"|"' + t2.InpatientEncounter_ + '"|"' + t2.RecordType2_ + '"|"' + t2.ArrivalDateTime_ + '"|"' + t2.AdmitToHospitalInpatient_ + '"|"' + t2.OrderAdmitDateTime_ + '"|"' + t2.EDDepartureDateTime_ + '"|"' + t2.AdmitDateTime_ + '"|"' + t2.DischargeDateTime_ + '"|"' + t2.AdmissionSource_ + '"|"' + t2.AdmissionType_ + '"|"' + t2.DischargeStatusDisposition_ + '"|"' + t2.LastKnownWellDateTime_ + '"|"' + t2.RoomNumber_ + '"|"' + t2.NonICULocation_ + '"|"' + t2.ICULocation_ + '"|"' + t2.ICUAdmissionOrTransfer_ + '"|"' + t2.ICUAdmitDateTime_ + '"|"' + t2.ICUTransferDateTime_ + '"|"' + t2.ICUArrivalDateTime_ + '"|"' + t2.ICUDischargeDateTime_ + '"|"' + t2.TransferFromLocation_ + '"|"' + t2.TransferFromLocationDateTime_ + '"|"' + t2.TransferToLocation_ + '"|"' + t2.TransferToLocationDateTime_ + '"|"' + t2.FillerField1_ + '"|"' + t2.FillerField2_ + '"|"' + t2.TimeOfDeath_ + '"|"' + t2.PreliminaryCauseOfDeath_ + '"|"' + t2.ClinicalTrial_ + '"|"' + t2.ClinicalTrialStartDateTime_ + '"|"' + t2.ClinicalTrialEndDateTime_ + '"|"' + t2.MedicareHICNumber_ + '"'
FROM        @Temp2 AS t2
WHERE       t2.EOC_ IS NOT NULL

INSERT INTO #diagnosisData (PatientID, Diagnosis)
SELECT  t3.EOC, '"' + t3.RecordType + '"|"' + t3.EOC + '"|"' + t3.Code + '"|"' + t3.CodeType + '"|"' + t3.Priority + '"|"' + t3.Status + '"|"' + t3.Severity + '"|"' + t3.StartDate + '"|"' + t3.EndDate + '"'
FROM    @Temp3 AS t3


DECLARE @resultsString          VARCHAR(MAX)        = NULL;
DECLARE @processingPatientsID   INT                 = NULL;
DECLARE @intermediateString     VARCHAR(MAX)        = NULL;

WHILE EXISTS (  SELECT  pd.PatientID
                FROM    #patientData AS pd
                WHERE   pd.isAppendedComplete = 0
             )
BEGIN
    SELECT TOP 1 @processingPatientsID = pd.PatientId,
                 @resultsString = ISNULL(@resultsString,'') + (pd.preAppendedPatientData + CHAR(13) + CHAR(10))
    FROM        #patientData AS pd
    WHERE       pd.isAppendedComplete = 0;

    SELECT  @intermediateString = COALESCE(@intermediateString + CHAR(13) + CHAR(10) ,'') + dd.Diagnosis
    FROM    #diagnosisData AS dd
    WHERE   dd.PatientId = @processingPatientsID;

    SELECT  @resultsString = @resultsString + @intermediateString + CHAR(13) + CHAR(10);

    --IMPORTANT, REQUIRED - MARK PATIENT RECORD AS PROCESSED
    UPDATE  pd
    SET     pd.isAppendedComplete = 1
    FROM    #patientData AS pd
    WHERE   pd.PatientId = @processingPatientsID;

    --IMPORTANT, REQUIRED
    SET @intermediateString = NULL;
END

SELECT @resultsString AS ClassicalWay;

Open in new window

0
 
RodgerSystems AnalystAuthor Commented:
I have the temp tables created before I INSERT them into the 2 two you had me create.  The patient temp table has almost 6,000 rows.  I have the PatientNumber as one field and then the other field is just all of the data I need for the output record.  The next temp table is just the PatientNumber and the the diagnosis code there are about 22,000 records.

Both tables have the isAppened set to 0 at first and then once it is processed it is changed to a 1.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Ok, so the two table variables used for concatenation (patientData and diagnosisData) has more than 1000 rows. This will have an impact on the overall performance, which is why I proposed using temporary tables for them as well.

The concatenation of such large data in a heap can cause severe fragmentation and slow down the query. Having clustered indexes will help in reducing the table scans and the locks required.
0
 
RodgerSystems AnalystAuthor Commented:
So are you saying that in the temp table I should do the concatenation and then ad them to the 2 new temp tables with all the variables put together?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
That would be good. But in addition to doing so, problem is also that the storage of large number of rows in table variables is inefficient.

I used table variables as a PoC/demo only. Do replace table variables with indexed temporary tables and concatenate before inserting into these temporary tables.
0
 
RodgerSystems AnalystAuthor Commented:
Nakul,

I have the query placing the values all in one field and it looks good.  I now have it printing out the different variables and I am getting the following after the 3 record.

Msg 8115, Level 16, State 2, Line 311
Arithmetic overflow error converting expression to data type int.

That line is this.
    SELECT TOP 1      @processingPatientsID = pd.PatientId,
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
It looks like you are getting a PatientId that is not an integer. Changing the data type for the patientId in the patientData table and the variable @processingPatientsId  to varchar(255) or nvarchar(255) should do the trick.
0
 
RodgerSystems AnalystAuthor Commented:
I am getting this error now.  I have it set as a VARCHAR(MAX) so I am not sure why it is trying to convert it to an INT

I have placed quotes around it I have moved it around nothing seems to help.

Msg 245, Level 16, State 1, Line 277
Conversion failed when converting the varchar value '"|55561003||"' to data type int.

DECLARE @TEMP3 TABLE	(PATIENT_ID		NVARCHAR(255),
						 DIAGNOSIS		VARCHAR(MAX))		

INSERT INTO @TEMP3
			SELECT DISTINCT
						PATIENT_ID	=	pv.patientaccountid,
						DIAGNOSIS	=	'"3"|"' + pv.patientaccountid + '"|"I10:' + Diagnosis.Code + '"|"' + Diagnosis.Priority + '"|55561003||"' +  Diagnosis.StartDate + '"|"' + Diagnosis.EndDate + '"'
		

Open in new window

0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
One of the columns being concatenated must be an integer (possibly Diagnosis.Priority or Diagnosis.Code?).

When faced different data types, SQL Server will implicitly try to convert all the values into a common type per rules of datatype precedence.

For columns of the integer datatype, CAST them explicitly into character data types and then concatenate (e.g. ...+ CAST(Diagnosis.Priority AS VARCHAR(50)) + ....)
0
 
RodgerSystems AnalystAuthor Commented:
Nakul,

Thanks again for your help that seemed to fix my issue.

Rodger
0
 
RodgerSystems AnalystAuthor Commented:
Thank you again!!!!
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Awesome! Glad to know I could help, and thank-you for the grade!
0
 
Jeffrey SmithCommented:
Hey Nakul, my name is Jeffrey & I have been working with Rodger on this project. For some reason all the data is not appending as it seems to be truncating in the Classical Way result. When we extract to pre appended data for patient information we have well over 5000 rows. Diagnosis has well over 30000 rows. We validated that there is a match between the two by importing into excel & used a VLookUp to validate both patient identifiers are in both, patient & Diagnosis. Any ideas on what we can analyze further? Your assistance is greatly appreciated!!

-Jeffrey
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Sure, let me work out a few options and revert back.
1
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Hello!

Just wanted to confirm my understanding - you need to concatenate the data in this fixed format and the ultimate goal is then to export it to a text file. Is my understanding correct?

If yes, how are you generating the text file? Are you taking the value of @resultsString into an object and then writing to a file in a C# code, or using SSIS to write to a file?

The @resultsString in my sample is a VARCHAR(MAX). Using VARCHAR(MAX) means that we have the capacity to hold upto 2GB of data. while it may be truncated when we SELECT it in SSMS, do rest assured that it is there.
0
 
Jeffrey SmithCommented:
Hi Nakul, yes, that is correct. All the raw data (separate queries) that we are writing need to append to each patient identifier or account # & output to a csv bar delimited fileQuery.sql.

Currently, we are making the bar delimited result by putting bars in between each field, copying and pasting into a csv file for upload tests. When we get there, we plan on automating the process.

Not to get overwhelming or anything but I have attached the current query. I believe we are using VARCHAR(MAX) too. Are you implying that even though it is being truncated in SQL Server, it could potentially be there but have hit our temporary hold of 2GB?

Thanks,
Jeffrey
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
...copying and pasting into a csv file for upload tests.
That's where the problem is :) - SSMS only displays a fixed number of characters.
...but have hit our temporary hold of 2GB?
I doubt it - 2GB is quite a large value for text data :). The restriction you are facing is with the tool (SSMS) and not the T-SQL code.

What you can do is try to direct export to a text file (again, not by using the "Results to Text" or "Results to File" options of SSMS) by using bulk transfer technologies like BCP or SSIS.

The example below shows how to encapsulate your code into a stored procedure and export the file using BCP (NOTE: I have used my sample code in this example - please replace with your code accordingly).

Code to create the procedure (I am using the tempdb here, but you will need to use your database, give the SP a name of your choice and paste your existing code into the SP):
USE tempdb;
GO
IF OBJECT_ID('dbo.ExportLargeData','P') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.ExportLargeData;
END
GO

IF OBJECT_ID('tempdb.dbo.patientData','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.patientData;
END
GO

IF OBJECT_ID('tempdb.dbo.diagnosisData','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.diagnosisData;
END
GO

CREATE TABLE dbo.patientData (PatientID                    BIGINT       PRIMARY KEY CLUSTERED,
                              PreAppendedPatientData       VARCHAR(MAX) ,
                              isAppendedComplete           BIT DEFAULT(0),
                             );
GO

CREATE TABLE dbo.diagnosisData (DiagnosisId                BIGINT       NOT NULL IDENTITY(1,1),
                                PatientID                  BIGINT       ,
                                Diagnosis                  VARCHAR(MAX) ,
                                isAppendedComplete         BIT DEFAULT(0),
                                PRIMARY KEY CLUSTERED (PatientID, DiagnosisID)
                            );
GO

INSERT INTO dbo.patientData (PatientID, PreAppendedPatientData)
--SELECT  t2.EOC_,  '"' + t2.RecordType_ + '"|"' + t2.EOC_ + '"|"' + t2.PatientID_  + '"|"' + t2.LastName_ + '"|"' + t2.FirstName_ + '"|"' + t2.BirthDate_ + '"|"' + t2.SexOnArrival_ + '"|"' + t2.Race_ + '"|"' + t2.Ethnicity_ + '"|"' + t2.PreferredLanguage_ + '"|"' + t2.PostalCode_ + '"|"' + t2.PaymentSourcePrimary_ + '"|"' + t2.PaymentSourceSecondary_ + '"|"' + t2.PaymentSourceSecondary2_ + '"|"' + t2.EDEncounter_ + '"|"' + t2.InpatientEncounter_ + '"|"' + t2.RecordType2_ + '"|"' + t2.ArrivalDateTime_ + '"|"' + t2.AdmitToHospitalInpatient_ + '"|"' + t2.OrderAdmitDateTime_ + '"|"' + t2.EDDepartureDateTime_ + '"|"' + t2.AdmitDateTime_ + '"|"' + t2.DischargeDateTime_ + '"|"' + t2.AdmissionSource_ + '"|"' + t2.AdmissionType_ + '"|"' + t2.DischargeStatusDisposition_ + '"|"' + t2.LastKnownWellDateTime_ + '"|"' + t2.RoomNumber_ + '"|"' + t2.NonICULocation_ + '"|"' + t2.ICULocation_ + '"|"' + t2.ICUAdmissionOrTransfer_ + '"|"' + t2.ICUAdmitDateTime_ + '"|"' + t2.ICUTransferDateTime_ + '"|"' + t2.ICUArrivalDateTime_ + '"|"' + t2.ICUDischargeDateTime_ + '"|"' + t2.TransferFromLocation_ + '"|"' + t2.TransferFromLocationDateTime_ + '"|"' + t2.TransferToLocation_ + '"|"' + t2.TransferToLocationDateTime_ + '"|"' + t2.FillerField1_ + '"|"' + t2.FillerField2_ + '"|"' + t2.TimeOfDeath_ + '"|"' + t2.PreliminaryCauseOfDeath_ + '"|"' + t2.ClinicalTrial_ + '"|"' + t2.ClinicalTrialStartDateTime_ + '"|"' + t2.ClinicalTrialEndDateTime_ + '"|"' + t2.MedicareHICNumber_ + '"'
--FROM        @Temp2 AS t2
--WHERE       t2.EOC_ IS NOT NULL
VALUES (1, 'TEST, PATIENT|123456789|1234567890|2016-10-11 13:05:25.000'),
       (2, 'TEST, PATIENT2|145236987|1236549870|2016-10-10 15:42:35.000');
GO

INSERT INTO dbo.diagnosisData (PatientID, Diagnosis)
--SELECT  t3.EOC, '"' + t3.RecordType + '"|"' + t3.EOC + '"|"' + t3.Code + '"|"' + t3.CodeType + '"|"' + t3.Priority + '"|"' + t3.Status + '"|"' + t3.Severity + '"|"' + t3.StartDate + '"|"' + t3.EndDate + '"'
--FROM    @Temp3 AS t3
VALUES (1, '3|146523.25648'),
       (1, '3|15648254.21'),
       (1, '3|148754263.33'),
       (2, '3|1546287.23'),
       (2, '3|1524684.2563'),
       (2, '3|16589640.285'),
       (2, '3|165482.547');
GO

CREATE PROCEDURE dbo.ExportLargeData 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @resultsString          VARCHAR(MAX)        = NULL;
    DECLARE @processingPatientsID   INT                 = NULL;
    DECLARE @intermediateString     VARCHAR(MAX)        = NULL;

    WHILE EXISTS (  SELECT  pd.PatientID
                    FROM    dbo.patientData AS pd
                    WHERE   pd.isAppendedComplete = 0
                 )
    BEGIN
        SELECT TOP 1 @processingPatientsID = pd.PatientId,
                     @resultsString = ISNULL(@resultsString,'') + (pd.PreAppendedPatientData + CHAR(13) + CHAR(10))
        FROM        dbo.patientData AS pd
        WHERE       pd.isAppendedComplete = 0;

        SELECT  @intermediateString = COALESCE(@intermediateString + CHAR(13) + CHAR(10) ,'') + dd.Diagnosis
        FROM    dbo.diagnosisData AS dd
        WHERE   dd.PatientId = @processingPatientsID;

        SELECT  @resultsString = @resultsString + @intermediateString + CHAR(13) + CHAR(10);

        --IMPORTANT, REQUIRED - MARK PATIENT RECORD AS PROCESSED
        UPDATE  pd
        SET     pd.isAppendedComplete = 1
        FROM    dbo.patientData AS pd
        WHERE   pd.PatientId = @processingPatientsID;

        --IMPORTANT, REQUIRED
        SET @intermediateString = NULL;
    END

    SELECT @resultsString AS ClassicalWay;
END
GO

Open in new window


Once the stored procedure is created in your database, you can execute the following from the command prompt. The file created should have all the data as you wanted.

--Now run this in the command prompt
--Please replace "MySQLServer" with the name of your SQL Server instance
--Also set the file path and stored procedure DB & name as necessary
BCP "EXEC tempdb.dbo.ExportLargeData" queryout E:\Temp\ExportedLargeData.txt -c -t -T -S MySQLServer

Open in new window


Let me know how it goes.
1
 
Jeffrey SmithCommented:
Nakul, thanks I am in the process of reviewing the script. For clarification purposes, what sort of values would we need to put within VALUES? I am a little unclear where these came from or what should go here?:


VALUES (1, 'TEST, PATIENT|123456789|1234567890|2016-10-11 13:05:25.000'),
       (2, 'TEST, PATIENT2|145236987|1236549870|2016-10-10 15:42:35.000');

VALUES (1, '3|146523.25648'),
       (1, '3|15648254.21'),
       (1, '3|148754263.33'),
       (2, '3|1546287.23'),
       (2, '3|1524684.2563'),
       (2, '3|16589640.285'),
       (2, '3|165482.547');

Much appreciated

-Jeffrey
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Because I do not have your tables or the data, I just used the VALUES clause to add some rows into my test table so that the core logic can be tested.

You can refer the script you supplied earlier. Basically you just need to create a stored procedure of the entire script and then use BCP to call the procedure.
1
 
Jeffrey SmithCommented:
Thanks for clarifying Nakul, this has been a huge help. I will let you know how it goes

-Jeffrey
1

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 14
  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now