--如果目标表已经存在,就用:--复制源表数据到目标表declare @源表 sysname,@目标表 sysname select @源表='要复制的表' ,@目标表='接受复制数据的表名'--复制处理 declare @fd varchar(8000) set @fd='' select @fd=@fd+',['+a.name+']' from syscolumns a join( select name from syscolumns where object_id(@源表)=id ) b on a.name=b.name where object_id(@目标表)=id and status<>0x80 select @fd=substring(@fd,2,8000) exec('insert into ['+@目标表+']('+@fd+') select '+@fd+' from ['+@源表+']')
在设计表中,把被插入表的id自增量属性去掉再插入 insert into table1 select * from table2
--复制源表数据到目标表(目标表不存在,要重新生成标识字段)declare @源表 sysname,@目标表 sysname select @源表='要复制的表' ,@目标表='接受复制数据的表名'--复制处理 declare @fd varchar(8000) set @fd='' select @fd=@fd+',['+a.name+']' +case status when 0x80 then '=identity(int,1,1)' else '' end from syscolumns a join( select name from syscolumns where object_id(@源表)=id ) b on a.name=b.name where object_id(@目标表)=id select @fd=substring(@fd,2,8000) exec('select ('+@fd+') into ['+@目标表+'] select '+@fd+' from ['+@源表+']')
--复制源表数据到目标表(目标表不存在,要重新生成标识字段)declare @源表 sysname,@目标表 sysname select @源表='要复制的表' ,@目标表='接受复制数据的表名'--复制处理 declare @fd varchar(8000) set @fd='' select @fd=@fd+',['+a.name+']' +case status when 0x80 then '=identity(int,1,1)' else '' end from syscolumns a join( select name from syscolumns where object_id(@源表)=id ) b on a.name=b.name where object_id(@目标表)=id select @fd=substring(@fd,2,8000) exec('select ('+@fd+') into ['+@目标表+'] select '+@fd+' from ['+@源表+']')
select @源表='要复制的表'
,@目标表='接受复制数据的表名'--复制处理
declare @fd varchar(8000)
set @fd=''
select @fd=@fd+',['+a.name+']'
from syscolumns a join(
select name from syscolumns where object_id(@源表)=id
) b on a.name=b.name
where object_id(@目标表)=id and status<>0x80
select @fd=substring(@fd,2,8000)
exec('insert into ['+@目标表+']('+@fd+') select '+@fd+' from ['+@源表+']')
insert into table1 select * from table2
select @源表='要复制的表'
,@目标表='接受复制数据的表名'--复制处理
declare @fd varchar(8000)
set @fd=''
select @fd=@fd+',['+a.name+']'
+case status when 0x80 then '=identity(int,1,1)'
else '' end
from syscolumns a join(
select name from syscolumns where object_id(@源表)=id
) b on a.name=b.name
where object_id(@目标表)=id
select @fd=substring(@fd,2,8000)
exec('select ('+@fd+') into ['+@目标表+'] select '+@fd+' from ['+@源表+']')
select @源表='要复制的表'
,@目标表='接受复制数据的表名'--复制处理
declare @fd varchar(8000)
set @fd=''
select @fd=@fd+',['+a.name+']'
+case status when 0x80 then '=identity(int,1,1)'
else '' end
from syscolumns a join(
select name from syscolumns where object_id(@源表)=id
) b on a.name=b.name
where object_id(@目标表)=id
select @fd=substring(@fd,2,8000)
exec('select ('+@fd+') into ['+@目标表+'] select '+@fd+' from ['+@源表+']')