問題描述

目標:傳入兩個參數(任務和主鍵)以生成表列表.獲取列表，然后動態構造插入語句，目的是將數據從生產環境復制到測試環境.換句話說，以編程方式執行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.

這篇關于T-SQL 動態地從測試轉移到生產的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！