Handling error in multiple insert script (sql)

Question

Hi all,

I need to insert about 500 images to database at once. I have a script, that builds multiple query script:

SELECT 'INSERT INTO [truvle].[dbo].[Ads_Images]([Img_adId],[Img_image]) 
     SELECT CONVERT(INT, ' + CAST([Ad_id] AS VARCHAR) + ')' +
   ',(SELECT * FROM OPENROWSET(BULK N''' +  [Ad_path] + ''', SINGLE_BLOB) as [something]) GO'
  FROM [truvle].[dbo].[Ads]

In result I get 500 "insert" queries like these:

...

INSERT INTO [truvle].[dbo].[Ads_Images]([Img_adId],[Img_image])        
SELECT CONVERT(INT, 1),(SELECT * FROM OPENROWSET(BULK N'some_path/Banners/58097048.gif', SINGLE_BLOB) as [something]) 
GO

INSERT INTO [truvle].[dbo].[Ads_Images]([Img_adId],[Img_image])        
SELECT CONVERT(INT, 2),(SELECT * FROM OPENROWSET(BULK N'some_path/Banners/10404012.gif', SINGLE_BLOB) as [something]) GO

INSERT INTO [truvle].[dbo].[Ads_Images]([Img_adId],[Img_image])        
SELECT CONVERT(INT, 3),(SELECT * FROM OPENROWSET(BULK N'some_path/Banners/10398875.gif', SINGLE_BLOB) as [something]) GO

...

But when I run this script and there is some error in some single query (file doesn't exists, for example) script stops working, so I have to remove wrong row and run it again. Does exist some way to skip defective query and keep whole script running?

Thanks, Katy.

Answer 1

IF you are using SQL SERVER, you can enclose each INSERT statement in a try catch block

BEGIN TRY
     { Your INSERT statement }
END TRY
BEGIN CATCH
     { INSERT to a LOG table} 
END CATCH

Since your queries are scripted, you can just add this error handling to the script as well.

Answer 2

Check if this helps. You can add this before each of your bulk insert statements-

DECLARE @doesExist INT

SET NOCOUNT ON
EXEC xp_fileexist 'C:\test.txt', @doesExist OUTPUT
SET NOCOUNT OFF

IF @doesExist = 1
BEGIN
BULK INSERT Test FROM 'C:\test.txt'
END