SQL Server：如果在Select中的函数中找不到表，则插入记录

I am using OPENROWSET for importing a .csv file into SQL Server 2014.

我正在使用OPENROWSET将.csv文件导入SQL Server 2014。

This is my query

这是我的疑问

SELECT 
    ufnGetChargeTypeId([Charge Type]) AS ChargeTypeId, [Description] 
FROM 
    OPENROWSET ('MSDASQL', 
                'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DBQ=D:\Files\;', 
                'SELECT * from [myfile.csv]' )

[Charge Type] is column header in the .csv file. ufnGetChargeTypeId is a function which returns ChargeTypeId if found in table ChargeType which is a reference table.

[Charge Type]是.csv文件中的列标题。 ufnGetChargeTypeId是一个函数，如果在作为参考表的表ChargeType中找到，则返回ChargeTypeId。

Working fine if [Charge Type] exist. I want to insert [Charge Type] in table if not found and return ChargeTypeId. User defined functions don't support INSERT/UPDATE in table.

如果存在[Charge Type]，则工作正常。如果找不到，我想在表中插入[Charge Type]并返回ChargeTypeId。用户定义的函数不支持表中的INSERT / UPDATE。

One solution is to use a stored procedure but I am calling function in Select statement and you can't exec stored procedure in Select statement.

一种解决方案是使用存储过程，但我在Select语句中调用函数，并且您不能在Select语句中执行存储过程。

Would you suggest any solution to my problem.

你能建议解决我的问题吗？

1 个解决方案

#1

You could first insert all missing types, then run your query:

您可以先插入所有缺少的类型，然后运行查询：

DECLARE @T TABLE([Charge Type] varchar(4000), [Description] text)

INSERT INTO @T
SELECT [Charge Type], [Description]
FROM 
    OPENROWSET ('MSDASQL', 
                'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DBQ=D:\Files\;', 
                'SELECT * from [myfile.csv]' )

INSERT INTO ChargeTypes ([Charge Type])
SELECT DISTINCT [Charge Type]
FROM @T
WHERE [Charge Type] NOT IN (SELECT [Charge Type] FROM ChargeTypes)

SELECT 
    ufnGetChargeTypeId([Charge Type]) AS ChargeTypeId, [Description] 
FROM @T