T-SQL 动态地从测试转移到生产

2021-09-10 00:00:00 sql tsql sql-server

目标:传入两个参数(任务和主键)以生成表列表.获取列表，然后动态构造插入语句，目的是将数据从生产环境复制到测试环境.换句话说，以编程方式执行EDIT TOP 200"所做的……但要快得多.

GOAL: Pass in two parameters (a task and a primary key) to generate a list of tables. Take the list, and then dynamically construct insert statements with the aim to copy data from a production environment to a test environment. In other words, do programmatically what 'EDIT TOP 200' does...but a lot faster.

问题:查询无限期地旋转和运行.应该只有大约 20-30 个表需要查询构建插入语句......所以我让它运行了大约 2 分钟，然后得出结论，我可能在某处有一个无限循环.请注意，此时我什至没有向测试数据库中插入任何内容.

PROBLEM: The query spins and runs indefinitely. There should only be about 20-30 tables that the query will need to construct insert statements for...so I let it go for about 2 minutes before concluding that I probably have an infinite loop somewhere. Note that I'm not even inserting anything into the test database at this point.

目前我只是想使用 RAISERROR 调用显示插入语句的 VALUES 部分.虽然最后阶段还没有实现，但我希望有人能帮我解决问题.

At the moment I'm just trying to display the VALUES portion of the insert statements using the RAISERROR call. While the endgame isn't implemented, I'm hoping someone can help me figure out the problem.

到目前为止:

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 EL.CustNo=@Custno
              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

推荐答案

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

是一个无限循环，因为你没有在它里面FETCH NEXT.

is an infinite loop, because you don't FETCH NEXT inside it.

相关文章