Hi!

Need to develop an SP to Bulk Insert 3 CSV files in Similar Formatted Tables from different file locations. However, am unaware in respect of how to break/end a process if any of the three files does not exist. In simple words a process like:

CREATE PROCEDURE [dbo].[FAQSP_PD_Work] @DataOne NVARCHAR(MAX), @DataTwo NVARCHAR(MAX), @DataThree NVARCHAR(MAX)

-- @DataOne, @DataTwo & @DataThree representing the three files' complete paths;

-- If any of the three files do not exist then simply end with an error message declaring the name of the file absent and end the procedure;

-- Otherwise Drop (If already exist) and Re-Create the 3 similar tables and BulkInsert &

-- Finally carry out rem

you can use xp_fileexists to check if the file exists and it will return 1 if the file is present else 0

so, you can use the if condition before going into the bulk insert

refer this https://www.mssqltips.com/sqlservertip/1272/file-validation-in-sql-server-with-xpfileexist-stored-procedure/

however, i think to run xp_fileexists - you need xp_cmds enabled to execute on your server - which may be not so recommended.

so,. you may want to do this using ssis package...and use file task system to check file exists and do the data tr

Hi!

Need to develop an SP to Bulk Insert 3 CSV files in Similar Formatted Tables from different file locations. However, am unaware in respect of how to break/end a process if any of the three files does not exist. In simple words a process like:

CREATE PROCEDURE [dbo].[FAQSP_PD_Work] @DataOne NVARCHAR(MAX), @DataTwo NVARCHAR(MAX), @DataThree NVARCHAR(MAX)

-- @DataOne, @DataTwo & @DataThree representing the three files' complete paths;

-- If any of the three files do not exist then simply end with an error message declaring the name of the file absent and end the procedure;

-- Otherwise Drop (If already exist) and Re-Create the 3 similar tables and BulkInsert &

-- Finally carry out rem

People have suggested xp_fileexists, but this procedure is undocumented why would be hesitant to use it. An alternative is to use a transaction:

BEGIN TRY
   BEGIN TRANSCTION

   SELECT @sql = 'BULK INSERT tbl1 FROM ' + quotename(@DataOne, '''') +
                  'WITH (FIELDTERMINATOR = '','')
   EXEC (@sql)

   SELECT @sql = 'BULK INSERT tbl2 FROM ' + quotename(@DataTwo, '''') +
                  'WITH (FIELDTERMINATOR = '','')
   EXEC (@sql)

   SELECT @sql = 'BULK INSERT tbl3 FROM ' + quotename(@DataThree, '''') +
                  'WITH (FIELDTERMINATOR = '','')
   EXEC (@sql)

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp -- http://www.sommarskog.se/error_handling/Part1.html#error_handler_sp
   RETURN 1133
END CATCH

If the last table doesn't exist, you have imported the two first, spending time and resources on that. That might be OK, but in case it isn't:

How about creating a global temp table, BCP-ing only one row into that, as an initial test to see if the tables exists. If all goes OK, we can keep doing the real imports. That allow you to use BCP as a way to check if the files exists - not utilizing any non-documented means.