提问人：Data Enthusiast 提问时间：10/4/2022 最后编辑：Thom AData Enthusiast 更新时间：10/12/2022 访问量：67
WHILE EXIST NOT WORKING - 动态地将数据插入到表中
WHILE EXIST NOT WORKING - To insert data to tables dynamically
问：
我正在尝试将数据从 txt 文件动态插入到现有表中。没有显示错误，它只是没有停止运行。我不确定我在 while 循环中犯的错误是什么。
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
答：
0赞
Dharanidhar
10/4/2022
#1
如果您的意图是循环，直到处理表中的所有记录，那么我认为您会使用
- 获取循环前的记录计数，并循环直到计数器超过记录总数。我知道我无法解决您的问题，但提供了解决方法。希望这对您有所帮助。
评论
0赞
Data Enthusiast
10/4/2022
相同的消息，没有完成插入。：消息 - 命令已成功完成。完成时间：2022-10-04T11：42：12.9594120+01：00
0赞
Data Enthusiast
10/4/2022
#2
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
评论
DELETE FROM #FILES WHERE FileName = @FILENAMEEND CATCH