我在查询某些数据库最新数据的总尺寸。
我创建要查询包含DBS一个表，然后遍历它来获取数据库名称和总次数运行迭代。
然后，我创造一个所需要的数据将被插入到一个不是Temptable。
我跑迭代抢信息，并将它推到不是Temptable为每个数据库。
迭代结束后，我无法从这个新创建的表拉值。
我写了一个小评论未来的代码的每一部分解释什么是我想要做什么，我希望发生的。
/*check if the #databases table is already present and then drop it*/
IF OBJECT_ID('tempdb..#databases', 'U') IS NOT NULL
begin
drop table #databases;
end
select ArtifactID into #databases from edds.eddsdbo.[Case]
where name like '%Review%'
/*Once this first statement has been run there will now be a
number column that is associated with the artificatID. Each database has an area that is
titled [EDDS'artifactID']. So if the artifactID = 1111111 then the DB would
be accessed at [EDDS1111111]*/
declare @runs int = 1; /*this will track the number of times iterated
over the result set*/
declare @max int = 0; /*this will be the limit*/
declare @databasename sysname='' /*this will allow the population of each
database name*/
/*check if your temp table exists and drop if necessary*/
IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL
begin
drop table #temptable;
end
/*create the temp table as outside the loop*/
create table #temptable(
fileSize dec,
extractedTextSize dec
)
while @runs<=@max
begin
select @max=count(*) from #databases;
/*the @max is now the number of databases inserted in to this table*/
/*This select statement pulls the information that will be placed
into the temptable. This second statment should be inside the loop. One time
for each DB that appeared in the first query's results.*/
/*begin the loop by assigning your database name, I don't know what the
column is called so I have just called it databasename for now*/
select top 1 @databasename = ArtifactID from #databases;
/*generate your sql using the @databasename variable, if you want to make
the database and table names dynamic too then you can use the same formula*/
insert into #temptable
select SUM(fileSize)/1024/1024/1024, SUM(extractedTextSize)/1024/1024
FROM [EDDS'+cast(@databasename as nvarchar(128))+'].[EDDSDBO].[Document] ed
where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,-
(day(getdate())),getdate()),106))'
/*remove that row from the databases table so the row won't be redone
This will take the @max and lessen it by one*/
delete from #databases where ArtifactID=@databasename;
/* Once the @max is less than 1 then end the loop*/
end
/* Query the final values in the temp table after the iteration is complete*/
select filesize+extractedTextSize as Gigs from #temptable
当这最后的SELECT语句运行到从#temptable的响应是一个单一的演出柱（如预期），但表本身是空拉值。
有事情发生，清除数据出表的，我被卡住。
我不知道如果我的错误是在语法或逻辑的一般错误，但任何帮助，将不胜感激。
做了一些调整，以格式化，但主要的问题是你的循环将永远不会运行。
你有@runs <= @Max，但@Max = 1，= 0开始@runs所以它永远不会循环
为了解决这个问题，你可以做一些不同的事情，但我设置循环之前的@Max，并在环刚添加1〜@runs每个循环，因为你知道你需要多少个循环运行之前@Max，只是将其添加到运行次数，做你的比较。
但一个注有更好的方法来做到这一点，那么你已经在路上。 把身份对你的#databases表，并在你的循环只是做其中databaseID = loopCount（当时你没有从表中删除）
--check if the #databases table is already present and then drop it
IF OBJECT_ID('tempdb..#databases', 'U') IS NOT NULL
drop table #databases;
--Once this first statement has been run there will now be a number column that is associated with the artificatID. Each database has an area that is
-- titled [EDDS'artifactID']. So if the artifactID = 1111111 then the DB would be accessed at [EDDS1111111]
select ArtifactID
INTO #databases
FROM edds.eddsdbo.[Case]
where name like '%Review%'
-- set to 0 to start
DECLARE @runs int = 0;
--this will be the limit
DECLARE @max int = 0;
--this will allow the population of each database name
DECLARE @databasename sysname = ''
--check if your temp table exists and drop if necessary
IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL
drop table #temptable;
--create the temp table as outside the loop
create table #temptable(
fileSize dec,
extractedTextSize dec
)
-- ***********************************************
-- Need to set the value your looping on before you get to your loop, also so if you dont have any you wont do your loop
-- ***********************************************
--the @max is now the number of databases inserted in to this table
select @max = COUNT(*)
FROM #databases;
while @runs <= @max
BEGIN
/*This select statement pulls the information that will be placed
into the temptable. This second statment should be inside the loop. One time
for each DB that appeared in the first query's results.*/
/*begin the loop by assigning your database name, I don't know what the
column is called so I have just called it databasename for now*/
select top 1 @databasename = ArtifactID from #databases;
/*generate your sql using the @databasename variable, if you want to make
the database and table names dynamic too then you can use the same formula*/
insert into #temptable
select SUM(fileSize)/1024/1024/1024, SUM(extractedTextSize)/1024/1024
FROM [EDDS'+cast(@databasename as nvarchar(128))+'].[EDDSDBO].[Document] ed
where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,- (day(getdate())),getdate()),106))
--remove that row from the databases table so the row won't be redone This will take the @max and lessen it by one
delete from #databases where ArtifactID=@databasename;
--Once the @max is less than 1 then end the loop
-- ***********************************************
-- no need to select from the table and change your max value, just change your runs by adding one for each run
-- ***********************************************
--the @max is now the number of databases inserted in to this table
select @runs = @runs + 1 --@max=count(*) from #databases;
end
-- Query the final values in the temp table after the iteration is complete
select filesize+extractedTextSize as Gigs from #temptable
这是第二个答案，但其替代像我上面提到的和更清洁张贴张贴作为替代答案，让他们独立
这是一个更好的方式做你的循环（不完全测试出来，因此还需要验证）。
但是，而不是从表中删除只是通过它使用ID添加一个ID给它和循环。 路少的步骤和更清洁。
--check if the #databases table is already present and then drop it
IF OBJECT_ID('tempdb..#databases', 'U') IS NOT NULL
drop table #databases;
--create the temp table as outside the loop
create table #databases(
ID INT IDENTITY,
ArtifactID VARCHAR(20) -- not sure of this ID's data type
)
--check if your temp table exists and drop if necessary
IF OBJECT_ID('tempdb..#temptable', 'U') IS NOT NULL
drop table #temptable;
--create the temp table as outside the loop
create table #temptable(
fileSize dec,
extractedTextSize dec
)
--this will allow the population of each database name
DECLARE @databasename sysname = ''
-- initialze to 1 so it matches first record in temp table
DECLARE @LoopOn int = 1;
--this will be the max count from table
DECLARE @MaxCount int = 0;
--Once this first statement has been run there will now be a number column that is associated with the artificatID. Each database has an area that is
-- titled [EDDS'artifactID']. So if the artifactID = 1111111 then the DB would be accessed at [EDDS1111111]
-- do insert here so it adds the ID column
INSERT INTO #databases(
ArtifactID
)
SELECT ArtifactID
FROM edds.eddsdbo.[Case]
where name like '%Review%'
-- sets the max number of loops we are going to do
select @MaxCount = COUNT(*)
FROM #databases;
while @LoopOn <= @MaxCount
BEGIN
-- your table has IDENTITY so select the one for the loop your on (initalize to 1)
select @databasename = ArtifactID
FROM #databases
WHERE ID = @LoopOn;
--generate your sql using the @databasename variable, if you want to make
--the database and table names dynamic too then you can use the same formula
insert into #temptable
select SUM(fileSize)/1024/1024/1024, SUM(extractedTextSize)/1024/1024
-- dont know/think this will work like this? If not you have to use dynamic SQL
FROM [EDDS'+cast(@databasename as nvarchar(128))+'].[EDDSDBO].[Document] ed
where ed.CreatedDate >= (select CONVERT(varchar,dateadd(d,- (day(getdate())),getdate()),106))
-- remove all deletes/etc and just add one to the @LoopOn and it will be selected above based off the ID
select @LoopOn += 1
end
-- Query the final values in the temp table after the iteration is complete
select filesize+extractedTextSize as Gigs
FROM #temptable
