Check if file exists before BULK INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check if file exists before BULK INSERT

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2012-03-27 : 15:35:06

Hi guys,

I need to make sure the file 'Test.txt' exists in the network location '\\myshare\abc$\DATA\' before proceeding with the BULK INSERT statement that will load the contents of the file to a table in the database MYDB.

Based on what I have researched so far the xp_fileexist Stored Procedure is not recommended be used as it is an undocumented SP. I've also tried using xp_cmdshell but I am having a permission issue.

Any feedback will be greatly appreciated.

Thank you!
J

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-27 : 15:38:12

I do use xp_fileexist. And yeah, I know it's undocumented. MS uses it in some XPs so I'm not too worried about it going away.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 15:48:17

Who said?


SET NOCOUNT ON
DECLARE @t table ([File Exists] int, [File is a Directory] int, [Parent Directory Exists] int)

INSERT INTO @t ([File Exists], [File is a Directory], [Parent Directory Exists])
exec xp_fileexist 'C:\Test.bat'

IF (SELECT [File Exists] FROM @t ) = 1
  BEGIN
	PRINT 'File Exists'
  END
ELSE
  BEGIN
	PRINT 'File Does Not Exists'
  END
SET NOCOUNT OFF

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-27 : 16:27:35

Who said what?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-27 : 21:47:32

quote:
Originally posted by sqlslick
Based on what I have researched so far the xp_fileexist Stored Procedure is not recommended be used as it is an undocumented SP.J

Dat said

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources