Use Bulk Insert or use default data in SQL

Question

Need help knowing if the following is possible.

I am uploading supplies for new teachers. When a teacher is new, they can either use default supplies already in our system or they can send in a .csv file for custom supplies.

I have a script written to upload the .csv file and insert it into the DB. And I have a script for just inserting records for the new teacher if they are using our default values. I am wondering if these 2 scripts can be combined.

So, if the file exists in my @FPath, I would like to upload it into my #T table. If it doesn't exist, I want to insert the default values into my temp #T table.

I am familiar with IF THEN ELSE and I'm thinking that's what I'd use, but I don't know how to tell it to check if a file exists.

Here's what I have currently:

DECLARE @Query Varchar(2000), @TeacherKey Varchar(10) = 1234, @FPath Varchar(100) = 'FilePath_1234.csv'

CREATE TABLE #T ( SupplyName Varchar(50), PkgQuantity Varchar(5), Price Varchar(9))

-- If file exists, I want to upload it (IF @FPath exists THEN)

SELECT @Query = ' BULK INSERT #T FROM ''' + @FPath + ''' WITH (FIELDTERMINATOR ='','', ROWTERMINATOR = ''\n'', FIRSTROW = 2 )' EXEC (@Query)

-- Otherwise I will use default values already in the DB (ELSE)

INSERT #T ( SupplyName, PkgQuantity, Price) SELECT SupplyName, PkgQty, Price FROM Supply WHERE TeacherKey = 1111

Any help or guidance is much appreciated! Thanks!!

Answer 1

Typically for interacting with the file system of the SQL Server instance's host server you want to utilize the xp_* stored procedures in the master system DB.

xp_fileexist 'C:/yourdestination'

Is probably the quickest method you could use to qualify an IF/THEN condition switch.

Answer 2

Just wanted to document I figured this out. I did it using xp_fileexist.

DECLARE 
@Query Varchar(2000), 
@TeacherKey Varchar(10) = 1234, 
@FPath Varchar(100) = 'FilePath_1234.csv',
@IsExists Int

CREATE TABLE #T 
(SupplyName Varchar(50), PkgQuantity Varchar(5), Price Varchar(9))

EXEC xp_fileexist @FPath, @IsExists OUTPUT

IF @IsExists = 1 

BEGIN
SELECT @Query = ' 
BULK INSERT #T 
   FROM ''' + @FPath + ''' 
   WITH 
      (FIELDTERMINATOR ='','', 
      ROWTERMINATOR = ''\n'', 
      FIRSTROW = 2 
   )' 
EXEC (@Query)
END

ELSE

INSERT #T ( SupplyName, PkgQuantity, Price) 
SELECT SupplyName, PkgQty, Price  
FROM Supply WHERE TeacherKey = 1111