我正在尝试获取数据库中满足某些约束的表的名称列表。
有了这个名称列表,我想将数据复制到另一个数据库中的表中。
我认为我下面的循环除了一件事之外就可以工作。我不确定如何设置变量@tblName?
例如,假设下面是表名列表
1. tblA
2. tblB
3. tblZ
在我的第一个循环中,我只想将@tblName设置为 tblA。
我的代码
declare @numTbls int
declare @count int = 1
declare @tblName nvarchar(100)
set @numTbls = (select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME like '%somePattern%')
while @count <= @numTbls
begin
set @tblName = (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME like '%somePattern%')
exec ('insert into DATABASE_B.dbo.' + @tblName + ' select * from DATABASE_A.dbo.' + @tblName)
set @count = @count + 1
end
你真的不需要循环来做到这一点
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ISNULL(@SQL, '')
+ 'INSERT INTO DATABASE_B.dbo.' + QUOTENAME(TABLE_NAME)
+ ' SELECT * FROM DATABASE_A.dbo.' + QUOTENAME(TABLE_NAME) + ';' + char(13)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME like '%somePattern%'
PRINT @SQL
EXEC sp_executesql @SQL
注意:这是假设两个数据库中表的模式相同且列序列相同
下面应该可以解决您的问题(代码中的注释(。如果不清楚,请添加注释。
declare @numTbls int
declare @count int = 1
declare @tblName nvarchar(100)
-- create table var
declare @MY_TABLE table (ID int identity(1,1), TABLE_NAME nvarchar(100))
-- add your data to this table
insert into @MY_TABLE
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE'
and TABLE_NAME like '%somePattern%'
-- find max on this table
set @numTbls = (select count(*) from @MY_TABLE)
-- loop data
while @count <= @numTbls
begin
set @tblName = (select TABLE_NAME from @MY_TABLE where ID = @count)
exec ('insert into DATABASE_B.dbo.' + @tblName + ' select * from DATABASE_A.dbo.' + @tblName)
set @count = @count + 1
end