I want to import a files into a temp table and select a substring of the first record as the FileID. I then want to compare this FileID to the FileID column in my Pharmacy.PDEFileLog table. If there is a match and the UplaodedReturnFile column in my Pharmacy.PDEFileLog table is NULL, I want to update the table with certain values (see code below). If the FileID does not exist, I want to return a statemnt that the file does not exist and if the FileID does exists and the UplaodedReturnFile column in my Pharmacy.PDEFileLog table is 'TRUE', I want to return a statement saying that the file has already been uploaded. Here is my code:
SELECT SUBSTRING(strRecord, 17, 3) as FileID
FROM #tmpDDPS
IF NOT EXISTS (SELECT FileID, UploadedReturnFile
FROM Pharmacy.PDEFileLog
WHERE FileID = FileID AND UploadedReturnFile='TRUE')
BEGIN
UPDATE Pharmacy.PDEFileLog
SET UploadedReturnFile='TRUE',
DateUploadedReturnFile=GET
DATE(), UploadedBy=USER
WHERE FileID = FileID
END
ELSE
BEGIN
PRINT 'This file has already been uploaded to the server.'
END
The above code updates all of my records in the Pharmacy.PDEFileLog table if the FileID exists. Not sure where to go from here. Any help would be appreciated. Thx.
Start Free Trial