set nocount on
declare @fieldlist varchar(max)
DECLARE @table_name varchar(255)
declare @sql varchar(max)
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
select TABLE_NAME
from INFORMATION_SCHEMA.tables
where table_type not in ('view')
OPEN curName
WHILE 1=1
BEGIN
FETCH NEXT FROM curName INTO @table_name
if @@fetch_status <> 0 begin
break
end
set @fieldlist = ''
select @fieldlist = coalesce(@fieldlist + ',','') + rtrim(c.name)
from sys.columns c
join sys.objects o on o.object_id = c.object_id
where o.name = @table_name
and c.is_identity = 0
Select @fieldlist = SUBSTRING(@fieldlist,2,len(@fieldlist)-1)
set @sql = 'delete sql2008.qrc_new.dbo.' + @table_name
print @sql
exec (@sql)
set @sql = 'insert into sql2008.qrc_new.dbo.' + @table_name + ' (' + @fieldlist + ') '
set @sql = @sql + 'select ' + @fieldlist + ' from ' + @table_name
print @sql
exec (@sql)
END
CLOSE curName
DEALLOCATE curName