通过访问系统表,循环所有带有lasttime字段的表,需要用到动态SQL语句。 动态sql语句基本语法 1 :普通SQL语句可以用Exec执行 eg: Select * from tableName Exec('select * from tableName') Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg: declare @fname varchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。 Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fname varchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' Exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @num int, @sql nvarchar(4000) set @sql='select count(*) from tableName' exec(@sql) --如何将exec执行结果放入变量中? declare @num int, @sql nvarchar(4000) set @sql='select @a=count(*) from tableName ' exec sp_executesql @sql,N'@a int output',@num output select @num
inser into 数据库B.dbo.表名(字段列表) select 字段列表 from 数据库A.dbo.表名上面的我知道 可是我有好多表 难道每个表我都要写1个上面的语句到对应到B表里去吗? 100个表就要写100次 ??有没有好点的办法。 还有就是 感觉楼上的有点混分 。。之前看过几个帖子都有以上回答 能不能给点针对问题的讨论
用动态SQL语句,生成所有带有lasttime字段的表名,针对每个表生成:insert into B ... select where .. delete A where ...这样的语句。--所有带有lasttime字段的表select table_name from information_schema.columns where column_name = 'lasttime' group by table_name
declare @tab sysname, @date1 datetime, @date2 datetime set @date1 = ... set @date1 = ... declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime' open curTab fetch next from curTab into @tab while @@fetch_status = 0 begin exec (' if exists (select 1 from '+@tab+' where lasttime = '''+@date1+''') begin insert 数据库B..'+@tab+' select * from '+@tab+' where lasttime >= '+@date1+' and lasttime <= '''+@date2+''' delete @tab where lasttime >= @date1 and lasttime <= @date2 end ') fetch next from curTab into @tab end close curTab deallocate curTab
--写着玩... declare @var varchar(8000) set @var='' select @var=@var+'insert b库..'+a.id+' select * from '+a.id+' where '+a.name+' between ''2005-06-05'' and ''2005-06-07'';' from (select name,object_name(id)[id] from syscolumns where name='lasttime')a join (select name from sysobjects where type='u')b on b.name=a.idexec(@var)
declare @tab sysname, @date1 datetime, @date2 datetime set @date1 = '' set @date1 = ... declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime' open curTab fetch next from curTab into @tab while @@fetch_status = 0 begin print (' if exists (select 1 from '+@tab+' where lasttime = '''+@date1+''') begin insert 数据库B..'+@tab+' select * from '+@tab+' where lasttime >= '+@date1+' and lasttime <= '''+@date2+''' delete '+@tab+' where lasttime >= '+@date1+' and lasttime <= ''' +@date2 + ''' end ')
declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime' declare @date1 datetime, @date2 datetime open curTab set @date1 =2005-06-12 set @date2 = 2005-06-19 declare @tab sysname fetch next from curTab into @tab while @@fetch_status = 0 begin exec (' if exists (select * from '+@tab+' where lasttime = '''+@date2+''') begin insert b'+@tab+' select * from '+@tab+' where lasttime >= '+@date1+' and lasttime <= '''+@date2+''' delete @tab where lasttime >= @date1 and lasttime <= @date2 end ') fetch next from curTab into @tab end close curTab deallocate curTab上面是我改的 是不是改出问题了第一次看这样的SQL 以前米见过..改的地方 @date1 给值 set @date1 =2005-06-12 这个应该没有弄错吧.. 还有改了个 select * from '+@tab+' where lasttime = '''+@date2+''')原来是select 1 from '+@tab+' where lasttime = '''+@date2+''')我想可能打错了吧...
declare @tab sysname, @date1 varchar(10), @date2 varchar(10), @sql varchar(8000)set @date1 = '2007-06-01' set @date2 = '2007-07-01' declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime' open curTab fetch next from curTab into @tab while @@fetch_status = 0 begin set @sql = 'if exists (select 1 from '+@tab+' where lasttime = '''+@date1+''')' set @sql = @sql+char(13)+'begin' set @sql = @sql+char(13)+'insert 数据库B..'+@tab+' select * from '+@tab+' where lasttime >= '''+@date1+''' and lasttime <= '''+@date2+'''' set @sql = @sql+char(13)+'delete '+@tab+' where lasttime >= '''+@date1+''' and lasttime <= '''+@date2+ '''' set @sql = @sql+char(13)+'end'+char(10) print @sql fetch next from curTab into @tab end close curTab deallocate curTab
if exists (select 1 from tab1 where lasttime = '2007-06-01') begin insert btab1 select * from tab1 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01' delete tab1 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01' endif exists (select 1 from tab2 where lasttime = '2007-06-01') begin insert btab2 select * from tab2 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01' delete tab2 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01' end -------- 以上是打印出来的 我继续看
动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sql nvarchar(4000)
set @sql='select count(*) from tableName'
exec(@sql)
--如何将exec执行结果放入变量中? declare @num int, @sql nvarchar(4000)
set @sql='select @a=count(*) from tableName '
exec sp_executesql @sql,N'@a int output',@num output
select @num
select 字段列表
from 数据库A.dbo.表名上面的我知道 可是我有好多表
难道每个表我都要写1个上面的语句到对应到B表里去吗?
100个表就要写100次
??有没有好点的办法。
还有就是 感觉楼上的有点混分
。。之前看过几个帖子都有以上回答
能不能给点针对问题的讨论
delete A where ...这样的语句。--所有带有lasttime字段的表select table_name
from information_schema.columns
where column_name = 'lasttime'
group by table_name
set @date1 = ...
set @date1 = ...
declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime'
open curTab
fetch next from curTab into @tab
while @@fetch_status = 0
begin
exec ('
if exists (select 1 from '+@tab+' where lasttime = '''+@date1+''')
begin
insert 数据库B..'+@tab+' select * from '+@tab+' where lasttime >= '+@date1+' and lasttime <= '''+@date2+'''
delete @tab where lasttime >= @date1 and lasttime <= @date2
end
')
fetch next from curTab into @tab
end
close curTab
deallocate curTab
declare @var varchar(8000)
set @var=''
select @var=@var+'insert b库..'+a.id+' select * from '+a.id+' where '+a.name+' between ''2005-06-05'' and ''2005-06-07'';' from
(select name,object_name(id)[id] from syscolumns where name='lasttime')a
join
(select name from sysobjects where type='u')b
on b.name=a.idexec(@var)
用不来..
Limpire(昨夜小楼) 的一直在出错 我改了好几次 都没有用..
为什么使用的时候一直在报 错啊..
\
服务器: 消息 170,级别 15,状态 1,行 4
第 4 行: '8' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,行 5
必须声明变量 '@tab'。
服务器: 消息 170,级别 15,状态 1,行 4
第 4 行: '8' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,行 5
必须声明变量 '@tab'。
可是有申明过的.
set @date1 = ''
set @date1 = ...
declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime'
open curTab
fetch next from curTab into @tab
while @@fetch_status = 0
begin
print ('
if exists (select 1 from '+@tab+' where lasttime = '''+@date1+''')
begin
insert 数据库B..'+@tab+' select * from '+@tab+' where lasttime >= '+@date1+' and lasttime <= '''+@date2+'''
delete '+@tab+' where lasttime >= '+@date1+' and lasttime <= ''' +@date2 + '''
end
')
declare @date1 datetime, @date2 datetime
open curTab
set @date1 =2005-06-12
set @date2 = 2005-06-19
declare @tab sysname
fetch next from curTab into @tab
while @@fetch_status = 0
begin
exec ('
if exists (select * from '+@tab+' where lasttime = '''+@date2+''')
begin
insert b'+@tab+' select * from '+@tab+' where lasttime >= '+@date1+' and lasttime <= '''+@date2+'''
delete @tab where lasttime >= @date1 and lasttime <= @date2
end
')
fetch next from curTab into @tab
end
close curTab
deallocate curTab上面是我改的
是不是改出问题了第一次看这样的SQL 以前米见过..改的地方 @date1 给值 set @date1 =2005-06-12 这个应该没有弄错吧..
还有改了个 select * from '+@tab+' where lasttime = '''+@date2+''')原来是select 1 from '+@tab+' where lasttime = '''+@date2+''')我想可能打错了吧...
set @date2 = '2007-07-01'
declare curTab cursor for select a.name from sysobjects a join syscolumns b on a.id = b.id where b.name = 'lasttime'
open curTab
fetch next from curTab into @tab
while @@fetch_status = 0
begin
set @sql = 'if exists (select 1 from '+@tab+' where lasttime = '''+@date1+''')'
set @sql = @sql+char(13)+'begin'
set @sql = @sql+char(13)+'insert 数据库B..'+@tab+' select * from '+@tab+' where lasttime >= '''+@date1+''' and lasttime <= '''+@date2+''''
set @sql = @sql+char(13)+'delete '+@tab+' where lasttime >= '''+@date1+''' and lasttime <= '''+@date2+ ''''
set @sql = @sql+char(13)+'end'+char(10)
print @sql
fetch next from curTab into @tab
end
close curTab
deallocate curTab
begin
insert btab1 select * from tab1 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01'
delete tab1 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01'
endif exists (select 1 from tab2 where lasttime = '2007-06-01')
begin
insert btab2 select * from tab2 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01'
delete tab2 where lasttime >= '2007-06-01' and lasttime <= '2007-07-01'
end
--------
以上是打印出来的
我继续看