如何把一個表的字段全加到另一個表？

[kokokokokoko]

如題

[子陌红尘]

在一个已经存在的表的结构上增加新的字段，这些字段来源于另外一个表？

[kokokokokoko]

對

[xiaoku]

先得到第一个表的create table语句脚本,去掉几个create table和()

然后再alter table b
add ...

[gahade]

--将n中的全部字段加到m中

create table m(a int,b int)
create table n(c int,d int)

declare @sql varchar(2000)
set @sql = ''
--取最大字段号
declare @maxcolid int
select @maxcolid = max(colid)+1 from syscolumns where id = object_id('m')

--生成表结构
select * into temps from syscolumns where 1=2
select @sql = 'alter table temps add id_identity int identity('+rtrim(@maxcolid)+',1)'
exec(@sql)

--自动增长生成临时表
insert into temps(name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset, bitpos, reserved, colstat, cdefault, domain, number, colorder, autoval, offset, collationid, language)
select name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset, bitpos, reserved, colstat, cdefault, domain, number, colorder, autoval, offset, collationid, language
from syscolumns
where id=object_id('n')

--允许修改系统表
exec sp_configure 'allow update',1
reconfigure with override

insert into syscolumns(name, id, xtype, typestat, xusertype, length, xprec, xscale, colid, xoffset, bitpos, reserved, colstat, cdefault, domain, number, colorder, autoval, offset, collationid, language)
select name, object_id('m'), xtype, typestat, xusertype, length, xprec, xscale, id_identity, xoffset, bitpos, reserved, colstat, cdefault, domain, id_identity, colorder, autoval, offset, collationid, language
from temps

--禁止修改系统表
exec sp_configure 'allow update',0
reconfigure with override

drop table temps
drop table m
drop table n

--查看结果
select * from syscolumns where id = object_id('m')

[stou]

看都看不懂,UP

[luxi0194]

select * into tablea from tableb ,然后delete from tablea

[xyxfly]

create table ta
(a int,
b varchar(10))

create table tb
(c int,
d int)

SELECT
字段序号=a.colorder,
字段名=a.name,
类型=b.name
into #T
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where d.name='tb'
order by a.id,a.colorder


declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' alter table ta add '+字段名+' '+类型 from #t

--print @sql
exec(@sql)
select * from ta
drop table #T,ta,tb

[marco08]

学习