迭代后从临时表中提取值 [英] Pulling Values From Temp Table After An Iteration
问题描述
我正在查询某些数据库中最近数据的总大小.
I'm querying for the total sizes of recent data in certain databases.
我创建了一个包含要查询的数据库的表，然后对其进行迭代以获取数据库名称和运行迭代的总次数.
I create a table containing the DBs to be queried then iterate over it to get the DB names and total number of times to run the iteration.
然后我创建一个临时表，将所需的数据插入其中.
I then create a temptable where the needed data will be inserted into.
我运行迭代以获取信息并将其推入每个数据库的临时表中.
I run the iteration to grab the information and push it into the temptable for each database.
迭代完成后，我无法从此新创建的表中提取值.
After the iteration finishes I'm not able to pull the values from this newly created table.
我在代码的每个部分旁边写了一个小注释，解释了我正在尝试做的事情以及我期望发生的事情.
I wrote a little comment next to each portion of code explaining what I'm trying to do and what I expect to happen.
/*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
当该最终选择语句运行以从#temptable中提取值时，响应为单个gigs列(如预期的那样)，但表本身为空白.
When that final select statement runs to pull values from #temptable the response is a single gigs column(as expected) but the table itself is blank.
正在清除表中的数据，我被卡住了.
Something is happening to clear the data out of the table and I'm stuck.
我不确定我的错误是语法还是逻辑上的一般错误，但是任何帮助将不胜感激.
I'm not sure if my error is in syntax or a general error of logic but any help would be greatly appreciated.
推荐答案
对格式进行了一些调整，但主要问题是循环永远不会运行.
Made a few tweaks to formatting but main issue is your loop would never run.
您具有@runs< = @max，但是@max = 1且@runs = 0，因此它永远不会循环
You have @runs <= @max, but @max = 1 and @runs = 0 at start so it will never loop
要解决此问题，您可以做一些不同的事情，但是我设置了@max before循环，并且在循环中，每个循环只向@runs加了1，因为您知道在循环运行之前需要@max多少，并且将其添加到运行次数中，然后进行比较.
To fix this you can do a couple different things but I set the @max before loop, and in the loop just added 1 to @runs each loop, since you know how many you need @max before loop runs, and just add it to number of runs and do your compare.
但是请注意，有比您拥有的方法更好的方法.将身份放在您的#databases表上，然后在循环中执行databaseID = loopCount(然后不必从表中删除)
But one NOTE there are much better ways to do this then the way you have. Put identity on your #databases table, and in your loop just do where databaseID = loopCount (then you dont have to delete from the table)
--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
这篇关于迭代后从临时表中提取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
