T-SQL从测试移动到生产

Question

目标： 传递两个参数（任务和主键）以生成表列表。获取列表，然后动态构建插入语句，旨在将数据从生产环境复制到测试环境。换句话说，以编程方式做什么'编辑TOP 200'呢......但速度更快。

问题：查询旋转并无限期地运行。应该只有大约20-30个表，查询将需要为...构建插入语句，所以我让它走了大约2分钟，然后才得出结论，我可能有一个无限循环的地方。请注意，我现在甚至没有在测试数据库中插入任何内容。

目前我只是试图使用RAISERROR调用显示插入语句的VALUES部分。虽然最终没有实施，但我希望有人能够帮助我找出问题所在。

迄今：

USE MAINDB 
DECLARE @PK int = 1000, 
@TaskName nvarchar(50) = 'TASK', 
@curTable nvarchar(75), 
@curRow nvarchar(75), 
@tmpStatement nvarchar(500), 
@tmpInsert nvarchar(500) 

RAISERROR('Retrieving Tables',0,1) WITH NOWAIT 
DECLARE TableCursor CURSOR LOCAL FOR 

    SELECT DISTINCT TOP 2 PRMPTTBL.tTable as PromptTable 
     FROM THING1 TK INNER JOIN THING2 SC ON TK.tkNo=SC.tkNo 
       INNER JOIN Component EL on EL.scNo=SC.scNo    
       LEFT OUTER JOIN Field FLD1 on FLD1.cfNo=EL.cfNoPrompt1    
       LEFT OUTER JOIN MyTableTable MTTTBL on MTTTBL.tbNo=FLD1.tbNo 

     WHERE [email protected] 
       AND (MTTTBL.tTable is not NULL AND MTTTBL.tTable not in('OneTableIDontWant')) 
       AND MTTTBL.tTable not like '%[_]d%' --eliminate any tables that are actually views 
       AND EL.cfNo > 0 
       AND TK.Description like @TaskName 

RAISERROR('Table',0,1) WITH NOWAIT 
OPEN TableCursor 
FETCH NEXT FROM TableCursor INTO @curTable 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @tmpStatement = 'SELECT TOP 5 * FROM [MYCONN].TEST_MYDB.dbo.' + @curTable + ' where PK=' + Cast(@PK as nvarchar(10)) 
    EXEC (@tmpStatement) 

    IF @@ROWCOUNT = 0 
    BEGIN 
     DECLARE RowCursor CURSOR LOCAL FOR 
     SELECT COLUMN_NAME 
     FROM REALDB.INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = @curTable 

     RAISERROR('Row',0,1) WITH NOWAIT 
     OPEN RowCursor 
     FETCH NEXT FROM RowCursor INTO @curRow 
     WHILE @@FETCH_STATUS = 0 
     BEGIN 
      SET @tmpInsert = @tmpInsert + ',' + @curRow 
     END 

     IF RIGHT(@tmpInsert,1) = ',' SET @tmpInsert = LEFT(@tmpInsert,LEN(@tmpInsert) -1) 
     RAISERROR(@tmpInsert,0,1) WITH NOWAIT 

     CLOSE RowCursor 
     DEALLOCATE RowCursor 
     SET @tmpInsert = '' 
     FETCH NEXT FROM RowCursor INTO @curRow 
    END 


    FETCH NEXT FROM TableCursor INTO @curTable 
END 

CLOSE TableCursor 
DEALLOCATE TableCursor 

Answer 1

WHILE @@FETCH_STATUS = 0 
    BEGIN 
     SET @tmpInsert = @tmpInsert + ',' + @curRow 
    END 

是一个无限循环，因为你不FETCH NEXT里面。