declare @strSourceTable varchar(50)
declare @strTargetTable varchar(50)
declare @strTemp varchar(60) --save column name
declare @strSql varchar(4000)
declare @strAllColumns varchar(4000)
set @strSourceTable = 'bdetail'
set @strTargetTable = 'detail'
set @strAllColumns = ' '
declare MyCursor cursor
for select name from syscolumns where id = object_id(@strSourceTable)
open MyCursor
fetch next from MyCursor
into @strTemp
WHILE @@FETCH_STATUS = 0
begin
set @strAllColumns = @strAllColumns + @strTemp + ', '
fetch next from MyCursor
into @strTemp
end
close MyCursor
Deallocate MyCursor
--print @strAllColumns
set @strAllColumns = left(@strAllColumns , len(@strAllColumns) - 1)
set @strSql = 'insert into '+ @strTargetTable + '( ' + @strAllColumns +') select ' + @strAllColumns +' from ' + @strSourceTable
--print @strSql
exec( @strSql)