CochiseCounty
asked on
Using While in Stored procedure
I am trying to import a text file to an SQL table.
I would like to check if the text file exists, if it exists, do the import, exit, if not, wait for 5 seconds, check again, and I want to loop through this process 5 times.
Here is my code, but it seems not working. Please help. Thanks
SET @TextFile = 'C:\ClaimsInquiry\Inbox\'+ @ProviderN umber+'.Lo gin.txt'
WHILE @t = 6
BEGIN
exec master..xp_fileexist @TextFile, @i out
IF @i=1
BEGIN
SET @WriteToDB = "BULK INSERT Inbox_Login FROM @TextFile WITH (FIELDTERMINATOR = ';') "
EXEC (@WriteToDB)
BREAK
END
ELSE
PRINT 'NOTHING'
WAITFOR DELAY '00:00:03'
CONTINUE
END
I would like to check if the text file exists, if it exists, do the import, exit, if not, wait for 5 seconds, check again, and I want to loop through this process 5 times.
Here is my code, but it seems not working. Please help. Thanks
SET @TextFile = 'C:\ClaimsInquiry\Inbox\'+
WHILE @t = 6
BEGIN
exec master..xp_fileexist @TextFile, @i out
IF @i=1
BEGIN
SET @WriteToDB = "BULK INSERT Inbox_Login FROM @TextFile WITH (FIELDTERMINATOR = ';') "
EXEC (@WriteToDB)
BREAK
END
ELSE
PRINT 'NOTHING'
WAITFOR DELAY '00:00:03'
CONTINUE
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works perfect. Thanks so much!
CochiseCounty:
> It works perfect. Thanks so much!
You're welcome. I think the main problem was that you weren't incrementing @t so that it eventually gets to 5 and stops executing.
-D*
> It works perfect. Thanks so much!
You're welcome. I think the main problem was that you weren't incrementing @t so that it eventually gets to 5 and stops executing.
-D*
2. Have you tried executing your @WriteToDB string in Query Analyzer?
3. What error messages are you getting?
4. Is 'NOTHING' getting printed?