sql - 如果文件路径为空或文件不存在，如何不更新列？

Question

我正在用这个更新一列：

UPDATE myTable
SET myFile = (SELECT myFile.BulkColumn  FROM OPENROWSET(BULK N'D:\Text3.txt', SINGLE_BLOB) AS myFile) 
WHERE id = 2

如果文件 Text3.txt 不存在，如何不更新列并将旧文件保留在那里？一些喜欢：

SET myFile = COALESCE((SELECT myFile.BulkColumn  FROM OPENROWSET(BULK N'D:\Text3.txt', SINGLE_BLOB) AS myFile), myFile)
WHERE id = 2

标签： sqlsql-updateopenrowset

Answer 1

使用 Master.dbo.xp_fileexist 检查文件是否存在

Create Table #temp
(
   File_Exists  bit,
   File_is_Directory int,
   Parent_Directory_Exists bit
)

INSERT INTO #temp
EXEC Master.dbo.xp_fileexist N'D:\Text3.txt'

--1 means exists while 0 means not exists
IF 1=(SELECT File_Exists FROM #temp)
BEGIN
   UPDATE myTable
   SET myFile = (SELECT myFile.BulkColumn  FROM OPENROWSET(BULK N'D:\Text3.txt', SINGLE_BLOB) AS myFile) 
   WHERE id = 2

   SELECT * FROM myTable
END
ELSE
BEGIN
   SELECT * FROM myTable
END

DROP table #temp