SET NOCOUNT ON
DECLARE @filepath varchar(1000)
DECLARE @formatfile varchar(1000)
DECLARE @filepattern varchar(100)
DECLARE @dirquery varchar(2000)
DECLARE @bcpquery varchar(2000)
DECLARE @delquery varchar(2000)
DECLARE @tablename varchar(128)
DECLARE @max1 int
DECLARE @count1 int
DECLARE @filename varchar(100)
/* Set Variables Here*/
SET @count1 = 0
SET @filepath = '\\wmg-lab-server\recalldata\teleminderin\'
SET @formatfile = ' -f\\wmg-lab-server\recalldata\teleminderin\test.fmt'
SET @filepattern = '*.*'
CREATE TABLE #x (Name varchar(200))
SET @dirquery = 'master.dbo.xp_cmdshell '+char(39)+'dir '+@filepath+@filepattern+' /b'+char(39)
INSERT INTO #x EXEC (@dirquery) 
DELETE FROM #x WHERE NAME IS NULL OR NAME LIKE '%.fmt%'
IF (SELECT count(*) FROM #x) = 0 GOTO FINISH
SELECT IDENTITY(int,1,1) as ID, NAME INTO #y FROM #x
SET @max1 = (SELECT MAX(ID) FROM #y)
CREATE TABLE #resultlog (Results varchar(1000),[FileName] varchar(500))
DECLARE @Print varchar(1000)
--SELECT * FROM #Y
DECLARE @file varchar(100)
DECLARE curImport CURSOR FAST_FORWARD LOCAL
FOR
	SELECT [Name] FROM #Y
OPEN curImport
FETCH NEXT FROM curImport INTO @file
WHILE (@@FETCH_STATUS <> -1)
BEGIN
CREATE TABLE #log (Results varchar(1000))
SET @count1=@count1+1
SET @filename = @file /*(SELECT NAME FROM #y WHERE [id] = @count1)*/
SET @bcpquery = char(39)+'bcp WMGRecallSystem..TeleminderIn in '+@filepath
	+@filename+@formatfile+' -U sa -P sa'+char(39)
SET @bcpquery = 'master.dbo.xp_cmdshell ' + @bcpquery
INSERT INTO #log EXEC ( @bcpquery)
	IF NOT EXISTS (SELECT * FROM #log WHERE RESULTS LIKE '%error%')
		BEGIN
		SET @delquery = char(39)+'del '+@filepath+@filename+char(39)
		SET @delquery = 'master.dbo.xp_cmdshell ' + @delquery
		EXEC ( @delquery)
		INSERT INTO #resultlog ('Success',@filename)
		END
	ELSE IF EXISTS (SELECT * FROM #log WHERE RESULTS LIKE '%error%')
		BEGIN
		INSERT INTO #resultlog ('Failure',@filename)
		END
DROP TABLE #log
FETCH NEXT FROM curImport INTO @file
END
CLOSE curImport
DEALLOCATE curImport

DROP TABLE #Y
DROP TABLE #DLOG
GOTO THEEND

FINISH:
	SET @PRINT = 'No Data In Directory To Process'
	GOTO THEEND

THEEND:
DROP TABLE #X