如果要将各表的前10条插入到别一个数据库已经存在的空表中,只要: use 含有空表的库 insert into t1 select top 10 from 原库.dbo.t1 insert into t2 select top 10 from 原库.dbo.t2 insert into t3 select top 10 from 原库.dbo.t3 insert into t4 select top 10 from 原库.dbo.t4 ....
--在新库的查询分析器中执行 insert into tb1 select top 10 * from 原数据库名.dbo.tb1
--获取库中所有表及该表的记录数declare @sql varchar(8000) set @sql='select * from (' select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u' set @sql = left(@sql,len(@sql) - 10) + ')a' exec(@sql)/* --sql server 2000 自带库 pubs 的结果 name num ----------- ----------- titleauthor 25 stores 6 sales 21 roysched 86 discounts 3 jobs 14 pub_info 8 employee 43 authors 23 publishers 8 titles 18 */
--以上为查询,如需要插入另外的一个空表,例如:TB,字段对应为:name,numdeclare @sql varchar(8000) set @sql='insert into tb select * from (' select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u' set @sql = left(@sql,len(@sql) - 10) + ')a' exec(@sql)
declare @sql varchar(8000) set @sql='select * from (' select @sql=@sql+' select top 10 *, name = ''' + name + ''' from ['+name+'] union all ' from sysobjects where xtype='u' set @sql = left(@sql,len(@sql) - 10) + ')a' exec(@sql)error: Msg 207, Level 16, State 1, Line 1 Invalid column name 'na'. Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
declare @sql varchar(8000) set @sql='select * from (' select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u' set @sql = left(@sql,len(@sql) - 10) + ' ) a ' exec(@sql)也报错Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ')'.
use 含有空表的库
insert into t1 select top 10 from 原库.dbo.t1
insert into t2 select top 10 from 原库.dbo.t2
insert into t3 select top 10 from 原库.dbo.t3
insert into t4 select top 10 from 原库.dbo.t4
....
--在新库的查询分析器中执行
insert into tb1 select top 10 * from 原数据库名.dbo.tb1
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)/*
--sql server 2000 自带库 pubs 的结果
name num
----------- -----------
titleauthor 25
stores 6
sales 21
roysched 86
discounts 3
jobs 14
pub_info 8
employee 43
authors 23
publishers 8
titles 18
*/
set @sql='insert into tb select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)
set @sql='select * from ('
select @sql=@sql+' select top 10 *, name = ''' + name + ''' from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'na'.
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ' ) a '
exec(@sql)也报错Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.