详细题目:现有一个数据库,绝大部分表中都含有affiliated_unit(单位名)字段,要把所有affiliated_unit(单位名),以及记录此affiliated_unit(单位名)插入到一个测试表test中(测试表含有unit_name、table_name这两个字段,unit_name放单位名,table_name放表名)。相同的记录不插入(affiliated_unit(单位名)和表名都相同时为相同记录)以下方法所用时间太长,因为数据中的记录有几百万条以上。希望给出更优化的方法DECLARE @sql varchar(5000)
DECLARE @sql_begin varchar(200)
DECLARE @sql_end varchar(500)
set @sql_begin='
DECLARE @affiliated_unit varchar(500)
DECLARE @table_name varchar(500)
DECLARE TableCursor_1 CURSOR LOCAL FOR ';
set @sql_end='
OPEN TableCursor_1
FETCH NEXT FROM TableCursor_1 INTO @affiliated_unit,@table_name
WHILE @@FETCH_STATUS=0
BEGIN
IF not EXISTS (select 1 from test where unit_name=@affiliated_unit and table_name=@table_name)
insert into test(unit_name,table_name) values(@affiliated_unit,@table_name)
FETCH NEXT FROM TableCursor_1 INTO @affiliated_unit,@table_name
END
CLOSE TableCursor_1
DEALLOCATE TableCursor_1';DECLARE TableCursor CURSOR LOCAL FOR
SELECT 'SELECT affiliated_unit,'''+o.name+''' name FROM ['+o.name+'] where affiliated_unit is not NULL' as sql
FROM sysobjects o
WHERE o.xtype='U' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
set @sql=@sql_begin+@sql+@sql_end
EXEC( @sql )
FETCH NEXT FROM TableCursor INTO @sql
END CLOSE TableCursor -- 删除游标引用
DEALLOCATE TableCursor
DECLARE @sql_begin varchar(200)
DECLARE @sql_end varchar(500)
set @sql_begin='
DECLARE @affiliated_unit varchar(500)
DECLARE @table_name varchar(500)
DECLARE TableCursor_1 CURSOR LOCAL FOR ';
set @sql_end='
OPEN TableCursor_1
FETCH NEXT FROM TableCursor_1 INTO @affiliated_unit,@table_name
WHILE @@FETCH_STATUS=0
BEGIN
IF not EXISTS (select 1 from test where unit_name=@affiliated_unit and table_name=@table_name)
insert into test(unit_name,table_name) values(@affiliated_unit,@table_name)
FETCH NEXT FROM TableCursor_1 INTO @affiliated_unit,@table_name
END
CLOSE TableCursor_1
DEALLOCATE TableCursor_1';DECLARE TableCursor CURSOR LOCAL FOR
SELECT 'SELECT affiliated_unit,'''+o.name+''' name FROM ['+o.name+'] where affiliated_unit is not NULL' as sql
FROM sysobjects o
WHERE o.xtype='U' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
set @sql=@sql_begin+@sql+@sql_end
EXEC( @sql )
FETCH NEXT FROM TableCursor INTO @sql
END CLOSE TableCursor -- 删除游标引用
DEALLOCATE TableCursor
where c.id=o.id and o.xtype='U' and c.name='affiliated_unit'declare @sql varchar(8000),@newline varchar(30)
set @sql=''
set @newline=CHAR(10)+CHAR(13)select @sql=@sql+' insert test(unit_name,table_name) select distinct '+column_name+' ,'''+tb_name+''''+' from ['+tb_name+'] where affiliated_unit is not null '+@newline from #tt
--print @sqlexec @sql