我已经写了以下代码,以从我的数据库中的所有相应视图(v_
)填充/创建表(t_
)。我从使用光标来提高运行速度来转换了一个以前的脚本。我还能做些什么以使其表现更好?
任何帮助都将不胜感激。
请参阅我的代码:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_populate_tables_from_views]
@dropTables BIT
AS
DECLARE @viewName NVARCHAR(100);
DECLARE @formattedTableName NVARCHAR(100);
DECLARE @dropTableSql NVARCHAR(MAX);
DECLARE @viewId int
SET @viewId = (SELECT MIN(object_id) FROM sys.views)
WHILE @viewId IS NOT NULL
BEGIN
SET @viewName = (SELECT name FROM sys.views WHERE object_id = @viewId)
PRINT 'Time: ' + CONVERT(varchar, SYSDATETIME(), 121)
PRINT('Processing ' + @viewName)
SET @formattedTableName = REPLACE(@viewName,'v_','t_');
IF @dropTables = 1
BEGIN
SET @dropTableSql = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '''+@formattedTableName+''')) DROP TABLE '+@formattedTableName
EXEC (@dropTableSql);
END
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @formattedTableName))
BEGIN
EXEC('TRUNCATE TABLE '+@formattedTableName)
EXEC('INSERT INTO '+@formattedTableName +' SELECT * FROM '+ @viewName)
END
ELSE
BEGIN
EXEC ('SELECT * INTO '+@formattedTableName+' FROM '+@viewName);
END
SET @viewId = (SELECT MIN(object_id)
FROM sys.views
WHERE object_id > @viewId)
END
- 声明@sql为nvarchar(max)=''
- 选择@sql = @sql '
- 如果存在(select * select * from imement_schema.tables where table_name = [' 替换(name,'v _','t_'),'t_') '])
- 开始
- 截断表[' 替换(名称,'v _','t_') ']
- 插入' 替换(名称,'v _','t_') 'select * from' name '
- 结束
- 其他
- select * in' 替换(名称,'v _','t_') 'from' name name char(10)
- 来自sys.views
- 打印@sql
- exec(@sql)