SQL Server: Insert record if not found in table from a function in Select

Question

I am using OPENROWSET for importing a .csv file into 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.

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.

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.

Would you suggest any solution to my problem.

Answer 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