假设A,B表用ID字段连接declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+a.name+'=b.'+a.name
from syscolumns a,syscolumns b
where a.id=object_id('A')
and b.id=object_id('B')
and a.name=b.name
and a.xusertype=b.xusertype
and a.name<>'ID'exec('update A set '+@sql+' from B where A.ID=B.ID')
select @sql=isnull(@sql+',','')+a.name+'=b.'+a.name
from syscolumns a,syscolumns b
where a.id=object_id('A')
and b.id=object_id('B')
and a.name=b.name
and a.xusertype=b.xusertype
and a.name<>'ID'exec('update A set '+@sql+' from B where A.ID=B.ID')
解决方案 »
- 重复序号重新编号,2000和2005各如何操作?
- SQL高级模糊查询语句 高手请帮忙
- 那个效率高,大量数据是分配在多数据库还是分配到多表
- insert 语句报错,求解决呀...
- 关于SQL递归函数列出父级的所有子级(ID ParentID模式)
- 这个网站有没有贴图的地方?
- 一个SQL语句
- sqlserver中有没有类似oracle中dual的虚拟表,如果没有怎么查询类似字符串之类的东西?
- 巨菜的问题
- 学习SQL,大神来指导下
- 我用asp写的程序后台用sql2005,为什么我的ASP最多同时访问人数有三十人时就会很慢SLEEP最多只有二十个,谢谢大家
- *****我想在DOS下还原sql server数据库*****
declare @var_sql varchar(8000)
declare @var_stable varchar(128)
declare @var_otable varchar(128)
declare @var_fldlist varchar(4000)set @var_stable='lx1' --目标表名
set @var_otable='lx2' --源表名set @var_fldlist=''
select @var_fldlist=@var_fldlist + t1.[name] + ',' from (
select b.[name] from sysobjects as a inner join syscolumns as b on a.[id]=b.[id]
where a.xtype='u' and a.[name]=@var_stable
) as t1 inner join
(
select b.[name] from sysobjects as a inner join syscolumns as b on a.[id]=b.[id]
where a.xtype='u' and a.[name]=@var_otable
) as t2 on t1.[name]=t2.[name]set @var_fldlist=left(@var_fldlist,len(@var_fldlist)-1)
set @var_sql='insert into ' + @var_stable + '(' + @var_fldlist + ') select ' + @var_fldlist + ' from ' + @var_otable
print @var_sql