假设有两个表,T_A,T_B,假设T_A有一下几个字段(id int,name verchar(50)),
T_B有下面的几个字段(id int,name verchar(50),age int...),
现在要将T_B字段更新到T_A字段中,SQL怎样写,T_B的字段不确定的,能写出一条通用的SQL嘛?
T_B有下面的几个字段(id int,name verchar(50),age int...),
现在要将T_B字段更新到T_A字段中,SQL怎样写,T_B的字段不确定的,能写出一条通用的SQL嘛?
解决方案 »
- 求一个SQL!A字段相同时候合并B字段
- 求一个sql
- 修改数据表中的字段长度,SQL语句怎么写?
- 为什么我用ntext的数据类型,连2页word文档都放不下?
- 不知道这个想法是不是妄想
- 怎样改我写的SQL语句让执行的效率提高的问题
- 如何解决ACCESS数据库被下载的问题?
- 关于ResultSet.TYPE_SCROLL_INSENSITIVE ,ResultSet.CONCUR_READ_ONLY 这两个参数
- 哪里有SQL server 2000 下载?
- 关联表数据的删除--Foxpro高手来看看
- SQL2005导出的脚本如何在SQL2000下运行?
- oracle存储过程求教 如何将一个表的数据插入到另外一张表
select id,name from T_B
----两张表一起更新的语句
update lzhu set zaiwang=A.zaiwang
from kkkkk as A
where kkkkk.haoma=lzhu.haoma
----两张表一起更新的语句
update T_B set name=A.name,age=A.age
from T_A as A
where T_B.Id=A.Id
还有就是在B表和A表都有的属性,用B表的属性替换A表的属性
declare @name varchar(60)
declare @typename varchar(60)
declare @sql varchar(max)
set @sql=''
declare my_cursor cursor for
select a.[name] colname,b.[name] as coltype from syscolumns a, systypes b where a.xtype=b.type and id= object_id('tab_B')
open my_cursor
fetch next from my_cursor into @name,@typename
while(@@fetch_status=0)
begin
if exists(select 1 from syscolumns where id=object_id('tab_A') and [name]=@name)
begin
set @sql=@sql+' alter table tab_A drop column '+ @name+' alter table tab_A add '+@name+' '+@typename
end
else
begin
set @sql=@sql+' alter table tab_A add '+@name+' '+@typename
end
end
Fetch Next From my_cursor InTo @name,@typename
end
close my_cursor
deallocate my_cursor exec(@sql)
declare @tb table(row int,alter_table varchar(500))
declare @sql varchar(8000);
insert into @tb(row,alter_table)
select ROW_NUMBER()over(order by b.name) as row
,case when a.name IN ('int','datetime','float','bit','date','geography','geometry','hierarchyid','image','money','ntext','real','smalldatetime','smallint','smallmoney','sql_variant','text','timestamp','tinyint','uniqueidentifier','xml') then 'alter table CH_20110708_CGSCSOL add '+b.name+' '+a.name
when b.max_length=-1 then 'alter table CH_20110708_CGSCSOL add '+b.name+' '+a.name+'(MAX)'
when a.name in ('nvarchar','nchar') then 'alter table CH_20110708_CGSCSOL add '+b.name+' '+a.name +'('+CONVERT(varchar(50),b.max_length/2)+')'
when a.name in ('datetime2','datetimeoffset','time') then 'alter table CH_20110708_CGSCSOL add '+b.name+' '+a.name +'('+CONVERT(varchar(50),b.scale)+')'
when a.name in ('varchar','char','binary') then 'alter table CH_20110708_CGSCSOL add '+b.name+' '+a.name +'('+CONVERT(varchar(50),b.max_length)+')'
when a.name in ('decimal','numeric') then 'alter table CH_20110708_CGSCSOL add '+b.name+' '+a.name +'('+CONVERT(varchar(50),b.precision)+','+CONVERT(varchar(50),b.scale)+')' end as alter_table
from sys.types as a
inner join sys.columns as b
on a.user_type_id=b.user_type_id
where b.object_id=(
select object_id
from sys.tables
where name='CH_20110708_CGSKART'
)
and b.name not in (
select b.name
from sys.types as a
inner join sys.columns as b
on a.user_type_id=b.user_type_id
where b.object_id=(
select object_id
from sys.tables
where name='CH_20110708_CGSCSOL'
)
)
declare @count int,@i int
set @i=1
select @count=MAX(row) from @tb
while @i<=@count
begin
select @sql=alter_table from @tb where row=@i
exec(@sql)
set @i=@i+1
end
CH_20110708_CGSKART 为T_B
16楼语句只是在T_A中添加在T_B中没有的列