EXEC sp_msforeachdb N' IF N''?'' NOT LIKE ''USER%'' RETURN;
USE ?; INSERT BB SELECT 1, 1 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT 4, 0 UNION ALL SELECT 5, 0; '
少了一个判断EXEC sp_msforeachdb N' -- 不是需要的数据库, 则跳过 IF N''?'' NOT LIKE ''USER%'' RETURN;
USE ?; IF NOT EXISTS( -- 没有数据插入 SELECT * FROM BB) INSERT BB SELECT 1, 1 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT 4, 0 UNION ALL SELECT 5, 0; '
大致代码是(未测试):declare @i as int declare @cnt as int set @i = 1 set @cnt = 0while @i <= 200 begin exec('use ' + right('000' + cast(@i as varchar),3)) select @cnt = count(*) from bb if @cnt = 0 begin insert into bb values(1,1) insert into bb values(2,0) insert into bb values(3,0) insert into bb values(4,0) insert into bb values(5,0) end set @i = @i + 1 set @cnt = 0 end
你的问题不是已经说明了, USER01-200 的库都有吗? 怎么又变了?
不过你也可以再判断的啦EXEC sp_msforeachdb N' -- 不是需要的数据库, 则跳过 IF N''?'' NOT LIKE ''USER%'' RETURN;
USE ?; IF OBJECT_ID(''BB'') IS NOT NULL -- 判断表是否存在 EXEC(N'' IF NOT EXISTS( -- 没有数据插入 SELECT * FROM BB) INSERT BB SELECT 1, 1 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT 4, 0 UNION ALL SELECT 5, 0; ''); '
EXEC sp_msforeachdb N' -- 不是需要的数据库, 则跳过 IF N''?'' NOT LIKE ''USER%'' RETURN;
USE ?; IF NOT EXISTS( -- 没有数据插入 SELECT * FROM BB) INSERT BB SELECT 1, 1 UNION ALL SELECT 2, 0 UNION ALL SELECT 3, 0 UNION ALL SELECT 4, 0 UNION ALL SELECT 5, 0; ' 老大里的这个 IF NOT EXISTS( -- 没有数据插入 SELECT * FROM BB) 这 句就是判断表BB有没有数据的 这个就是没有数据执行下面的INSERT 语句就是 插入了
我试一下: USE master declare @str varchar(200) declare @dataname varchar(20) declare cr cursor fast_forward for select name+'.dbo.BB' from sysdatabases WHERE name='IT_PUBLIC'open cr fetch next from cr into @dataname while @@fetch_status<>-1 begin print @str exec('select top 1* from '+@dataname) if @@rowcount=0 begin select @str='insert into '+@dataname +' select 1 ,1 union all select 2,0 union all select 3,0 union all select 4,0 union all select 5,0' print @str exec('insert into '+@dataname +' select 1,1 union all select 2,0 union all select 3,0 union all select 4,0 union all select 5,0') end fetch next from cr into @dataname end close cr deallocate cr select *from IT_PUBLIC.dbo.BB
请看下面的 USE master declare @str varchar(200) declare @dataname varchar(20) declare cr cursor fast_forward for select name+'.dbo.BB' from sysdatabases open cr fetch next from cr into @dataname while @@fetch_status<>-1 begin print @str exec('select top 1* from '+@dataname) if @@rowcount=0 begin select @str='insert into '+@dataname +' select 1 ,1 union all select 2,0 union all select 3,0 union all select 4,0 union all select 5,0' print @str exec('insert into '+@dataname +' select 1,1 union all select 2,0 union all select 3,0 union all select 4,0 union all select 5,0') end fetch next from cr into @dataname end close cr deallocate cr
EXEC sp_msforeachdb N'
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'
-- 不是需要的数据库, 则跳过
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'
declare @cnt as int
set @i = 1
set @cnt = 0while @i <= 200
begin
exec('use ' + right('000' + cast(@i as varchar),3))
select @cnt = count(*) from bb
if @cnt = 0
begin
insert into bb values(1,1)
insert into bb values(2,0)
insert into bb values(3,0)
insert into bb values(4,0)
insert into bb values(5,0)
end
set @i = @i + 1
set @cnt = 0
end
你的问题不是已经说明了, USER01-200 的库都有吗? 怎么又变了?
-- 不是需要的数据库, 则跳过
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
IF OBJECT_ID(''BB'') IS NOT NULL -- 判断表是否存在
EXEC(N''
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'');
'
EXEC sp_msforeachdb N'
-- 不是需要的数据库, 则跳过
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'
老大里的这个
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
这 句就是判断表BB有没有数据的
这个就是没有数据执行下面的INSERT 语句就是 插入了
USE master
declare @str varchar(200)
declare @dataname varchar(20)
declare cr cursor fast_forward for
select name+'.dbo.BB' from sysdatabases WHERE name='IT_PUBLIC'open cr
fetch next from cr into @dataname
while @@fetch_status<>-1
begin
print @str
exec('select top 1* from '+@dataname) if @@rowcount=0
begin
select @str='insert into '+@dataname +'
select 1 ,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0'
print @str
exec('insert into '+@dataname +'
select 1,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0')
end fetch next from cr into @dataname
end
close cr
deallocate cr select *from IT_PUBLIC.dbo.BB
USE master
declare @str varchar(200)
declare @dataname varchar(20)
declare cr cursor fast_forward for
select name+'.dbo.BB' from sysdatabases open cr
fetch next from cr into @dataname
while @@fetch_status<>-1
begin
print @str
exec('select top 1* from '+@dataname) if @@rowcount=0
begin
select @str='insert into '+@dataname +'
select 1 ,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0'
print @str
exec('insert into '+@dataname +'
select 1,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0')
end fetch next from cr into @dataname
end
close cr
deallocate cr