T-SQL 动态地从测试转移到生产
- 作者: 我是农村姑娘
- 来源: 51数据库
- 2023-02-07
问题描述
目标:传入两个参数(任务和主键)以生成表列表.获取列表,然后动态构造插入语句,目的是将数据从生产环境复制到测试环境.换句话说,以编程方式执行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.