WHILE EXIST NOT WORKING - To insert data to tables dynamically-CodePudding

I am trying to insert data from txt files to existing tables dynamically. There is no error shown, it just doesn't stop running. I am not sure what the mistake I am doing in the while loop is.

IF OBJECT_ID('TEMPDB..#FILES') IS NOT NULL DROP TABLE #FILES

CREATE TABLE #FILES
(
FileName VARCHAR(MAX),
DEPTH VARCHAR(MAX),
[FILE] VARCHAR(MAX)
)
 
INSERT INTO #FILES
EXEC master.dbo.xp_DirTree '\\Server1.newdevlab.local\SQLBackups_2012\Internal\Nimisha\',1,1
Select * from #FILES


DECLARE @FILENAME VARCHAR(MAX),@SQL VARCHAR(MAX)
DECLARE @TABLENAME Varchar(12) 
SET NOCOUNT ON;
WHILE EXISTS (SELECT 1 FROM #FILES)
BEGIN
BEGIN TRY
   SET @FILENAME = (SELECT TOP 1 FileName FROM #FILES)
   SET @TABLENAME =  'Buddy.'   right(@TABLENAME, len(@TABLENAME)-6)

   SET @SQL = N'BULK INSERT '   @TABLENAME   ''  
              N' FROM ''\\Server1.newdevlab.local\SQLBackups_2012\Internal\Nimisha\'   @FILENAME  '''
   WITH (FIRSTROW = 2, FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'');'

   PRINT @SQL
   EXEC(@SQL)
  
   DELETE FROM #FILES WHERE FileName = @FILENAME
END TRY
   BEGIN CATCH
      PRINT 'Failed processing : '   @FILENAME
   END CATCH
END

CodePudding user response：

If your intention is to loop until you process all the records in the table, then I think you go with While (select count(*) from #Files) > 0

CodePudding user response：

USE xxxx

IF OBJECT_ID('TEMPDB..#FILES') IS NOT NULL DROP TABLE #FILES

CREATE TABLE #FILES ( FileName VARCHAR(100), DEPTH VARCHAR(100), [FILE] VARCHAR(MAX) )

INSERT INTO #FILES EXEC master.dbo.xp_DirTree '\server.newdevlab.local\SQLBackups_2012\Internal\xxx',1,1 Select * from #FILES

DECLARE @FILENAME VARCHAR(MAX) ,@SQLString VARCHAR(MAX) DECLARE @TABLENAME Varchar(50) DECLARE file_cursor CURSOR FOR SELECT FileName FROM #FILES OPEN file_cursor FETCH NEXT FROM file_cursor INTO @FileName

WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY SET @TABLENAME = SUBSTRING(('Buddy.' substring(@FILENAME,7, len(@FILENAME)-3)), 1 ,LEN(('Buddy.' substring(@FILENAME,7, len(@FILENAME)-3)))-4)
SET @SQLString = N'BULK INSERT ' @TABLENAME '' N' FROM ''\server.newdevlab.local\SQLBackups_2012\Internal\xxx' @FILENAME ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''\n'');'

-- SELECT @SQLString EXEC(@SQLString)

  END TRY
  
  BEGIN CATCH
  SELECT ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(),USER,GETDATE()
  END CATCH



  FETCH NEXT
  FROM file_cursor
  INTO @FileName
  END 
  CLOSE file_cursor
  DEALLOCATE file_cursor