BULK INSERT from many files, skip if file doesn't exist. 2

[SQLBI]

Hi,

I'm using BULK INSERT to import data from multiple sequentially numbered text files into my database.

My code works fine and all is well until a particular file doesn't exist.

DECLARE @Practice int
DECLARE @File varchar(255)
DECLARE @SQL varchar(8000)

SET @Practice = 101


WHILE @Practice <= (SELECT MAX(PracticeID)
			FROM Vets4Pets_Info..[List - Practices]) 
	BEGIN
		SET @File = 'E:\RxWorks\DataExtracts\' + CONVERT(char(3), @Practice) + '_C_Data.txt'
		PRINT @File
		
		SET @SQL = N'BULK INSERT Marketing..Client_Raw
				FROM ''' + @File + ''' 
						WITH 
						(	
						FIELDTERMINATOR = ' +  ''',''' + ', 
						ROWTERMINATOR = ' + '''\n''' + ',
						FIRSTROW = 2,
						KEEPNULLS
					        )'
		
		PRINT @SQL
		
		EXEC (@SQL)
	
		SET @Practice = @Practice + 1
	END

What i want is to skip the BULK INSERT process and increment the @Practice variable by 1 in the event that a particular file is missing. At present i have no '116' file, so the SP stops when the error occurs.

I've looked at BREAK and RETURN but i'm not sure they're what i'm after, in VB i'd use 'ON ERROR GOTO', is there a similar function in T-SQL?

Does anyone have any suggestions?

Cheers,
Leigh

Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?

 

[SQLDenis]

use the undocumented xp_fileexist proc

create table #temp (Filexists bit,FileIsdirectory bit,ParentDirectoryExists bit)

declare @File varchar(100)
select @File = 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt'
insert #temp
exec master..xp_fileexist @File
if not exists (select * from #temp where Filexists =1)
do stuff here
and truncate your table before going back to the begining of the loop code

Modify for your own use

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dhulbert]

If you put this into a DTS you can use a VBA script to populate a table with a list of files in the source directory then loop through the rows in that table to get your file names for the stored procedure


I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[gmmastros]

DECLARE @Practice int
DECLARE @File varchar(255)
DECLARE @SQL varchar(8000)
[!]Declare @FileExists Integer[/!]
SET @Practice = 101


WHILE @Practice <= (SELECT MAX(PracticeID)
            FROM Vets4Pets_Info..[List - Practices]) 
    BEGIN
        SET @File = 'E:\RxWorks\DataExtracts\' + CONVERT(char(3), @Practice) + '_C_Data.txt'
        PRINT @File
        
        [!]Exec Master..xp_fileExists @File, @FileExists Output
        If @FileExists = 1
          Begin[/!]
            SET @SQL = N'BULK INSERT Marketing..Client_Raw
                FROM ''' + @File + ''' 
                        WITH 
                        (    
                        FIELDTERMINATOR = ' +  ''',''' + ', 
                        ROWTERMINATOR = ' + '''\n''' + ',
                        FIRSTROW = 2,
                        KEEPNULLS
                            )'
        
            PRINT @SQL
        
            EXEC (@SQL)
          [!]End[/!]
        SET @Practice = @Practice + 1
    END

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

star for George since I didn't realize xp_fileExists had output parameters
exec master..sp_helptext 'xp_fileexist' doesn' help either ;-(

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBI]

Star for Denis and George!

Cheers guys!

Cheers,
Leigh

Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?