declare @name varchar(20),@type int,@length int,@modifystr varchar(500) select top 1 @name=[name],@type=xtype,@length=[length] from syscolumns where id in (select id from sysobjects where xtype='U' and name='替换表') and name not in( select name from syscolumns where id in (select id from sysobjects where xtype='U' and name='被修改表') ) set @modifystr='alter table add '+@name + (case when @type=56 then 'int' else 'varchar('+@length+')' end) exec(modifystr) --循环.... --还得考虑是否为空以及其他数据类型 --表说还有约束吧....
create table tb1(a1 int,a2 int) go create table tb2(b1 int,b2 int) go--先建一个tb3 select b1,b2,a1,a2 into tb3 from tb1,tb2 where 1>2 --删除tb2 drop table tb2 go --给tb3改名成tb2 sp_rename 'tb3','tb2'
select top 1 @name=[name],@type=xtype,@length=[length] from syscolumns where id in (select id from sysobjects where xtype='U' and name='替换表')
and name not in(
select name from syscolumns where id in (select id from sysobjects where xtype='U' and name='被修改表')
)
set @modifystr='alter table add '+@name + (case when @type=56 then 'int' else 'varchar('+@length+')' end)
exec(modifystr)
--循环....
--还得考虑是否为空以及其他数据类型
--表说还有约束吧....
SQL05环境下曾用存储过程写过,主要是找系统表关系。。
我印象当中的sql语句大概是insert into select * form tab1 to tab2(当然这个语句是错误的)
如果是前者:
insert into tb2(列1,列2....) select 列1,列2.... from tb
如果是后者:
select * into tb2 from tb
select * into tb2 from tb where 1>2
create tb1(id int,id2 int)
create tb2(is int,if int)
这两个表间的字段都没有重复的,我想把表tb1里面的两个字段差进去tb2中去
结果tb2里面有4个字段了,想要条比较简洁的sql语句
go
create table tb2(b1 int,b2 int)
go--先建一个tb3
select b1,b2,a1,a2 into tb3 from tb1,tb2 where 1>2
--删除tb2
drop table tb2
go
--给tb3改名成tb2
sp_rename 'tb3','tb2'
辛苦大家了,分不多,就给:perfectaction 30分。呵呵