Solved

Output query to a text file

Posted on 2016-10-11
28
94 Views
Last Modified: 2016-11-03
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
Comment
Question by:Rodger
  • 14
  • 10
  • 4
28 Comments
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41839814
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41839860
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
 
LVL 4

Author Comment

by:Rodger
ID: 41840093
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
 
LVL 4

Author Comment

by:Rodger
ID: 41840615
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41840813
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
 
LVL 4

Author Comment

by:Rodger
ID: 41841945
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41841953
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
 
LVL 4

Author Comment

by:Rodger
ID: 41841967
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41841995
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
 
LVL 4

Author Comment

by:Rodger
ID: 41842010
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41842036
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
 
LVL 4

Author Comment

by:Rodger
ID: 41842048
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41842072
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
 
LVL 4

Author Comment

by:Rodger
ID: 41842295
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41842371
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
 
LVL 4

Author Comment

by:Rodger
ID: 41842485
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
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41842518
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
 
LVL 4

Author Comment

by:Rodger
ID: 41843894
Nakul,

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

Rodger
0
 
LVL 4

Author Closing Comment

by:Rodger
ID: 41843895
Thank you again!!!!
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41843990
Awesome! Glad to know I could help, and thank-you for the grade!
0
 

Expert Comment

by:Jeffrey Smith
ID: 41870344
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41870357
Sure, let me work out a few options and revert back.
1
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41870464
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
 

Expert Comment

by:Jeffrey Smith
ID: 41870866
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41872316
...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
 

Expert Comment

by:Jeffrey Smith
ID: 41872662
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
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
ID: 41872694
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
 

Expert Comment

by:Jeffrey Smith
ID: 41872719
Thanks for clarifying Nakul, this has been a huge help. I will let you know how it goes

-Jeffrey
1

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
statistics before and after huge DEL/INS 3 16
How to calculate iops? 12 28
Caste datetime 2 25
Update in Sql 7 12
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now