get info from a variable of a huge set of databases with same schema and table name????
-
Hi, Can you please help with with this query? I need to set two columns as result: db_name and sum of Quantity. I need to use distributed query to go through each db_name from a huge list of 20000 tables with same table name dbName. Can you tell me what I did wrong below here? Thank you so much!!! You're guys are the BEST!!!
create table dbName
db_ID nvarchar(20),
db_Qualtity(20)
insert into dbName values ('327','543','324')declare @db_name nvarchar(20) = (
select db_ID from dbName
),
@sql nvarchar(max)set @sql '
select count(quatity)
from [ACH].'+@db_name+'.[dbo].[table01]'execute sp_executesql @sql
-
You need to either use Cursor
or try something like
@sql nvarchar(max)=''set @sql= @sql + '
select count(quatity) from [ACH].['+db_id+'].[dbo].[table01];' from dbName
execute sp_executesql @sql -
Thank you very much Ten!
Can you or anyone help the codes I have here? I'm trying to pull the total of files received for each databases. I have to get the Date in as criteria for Fiscal year. I'm trying to run on muiltiple databases with same table names and put them in the temp table.
When I run the code, it executed the @sql proc but it didn't put data into the temp table for some reasons. Can you please help me out with this? Thank you so much in advance!
-- Create Temp table for result
IF OBJECT_ID('tempdb..#Temp1Rel') IS NOT NULL BEGIN DROP TABLE #Temp1Rel END
-- set Fiscal Year Start Date
Declare @FYStartDate nvarchar(30) = '2015-10-01 00:00:00.000'
-- Set Fiscal Year End Date
Declare @FYEndDate nvarchar(30) = '2016-09-30 00:00:00.000'
-- Set Case ID
Declare @Case_Prefix nvarchar(4) ='THUR'
DECLARE @CaseID nvarchar(10) = (select cast(CaseID as nvarchar(10)) from [MainDB].[dbo].[AllCases] )
-------------------------------------------------
Declare @SQL nvarchar(max)
Set @SQL = @SQL + '
SELECT CaseID = @CaseID,
sum(filecount) as "Receivedfiles"
INTO #TEMP1Rel
FROM [' +
@Case_Prefix + @CaseID+'].DBO.Files z join [' + @Case_Prefix + @CaseID+'].[DBO].[Source] x on z.OriginatingSource=x.ID
where
x.DateSubmitted > = @FYStartDate and x.DateSubmitted <= @FYEndDate
;'
exec sp_executesql @sql
-------------------------------------------------
select * from #TEMP1Rel
-------------------------------------------------
--DROP TABLE #TEMP1Rel
; -
Question, what is the following trying to achieve?
DECLARE @CaseID nvarchar(10) = (select cast(CaseID as nvarchar(10)) from [MainDB].[dbo].[AllCases] )
Does your table, AllCases, only have one row in it? I have a feeling the answer is no, as otherwise your name is quite misleading, but if that is the case, you've misunderstood how variables work.
Also, Roshan is called Roshan not Ten Centuries. That is his rank (your login isn't SSC Rookie is it 😉 ).Edit: Why is there a code box at the end of my post..? O.o
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, March 14, 2017 9:52 AM
CaseID is a set of random database ID names I got from this main table I have and I created a list of them and put here. I need a total simple count rows of table Files in column filecount. Sorry. Excuse my poor English.
-
You can't set a list of values to a variable. If you want to do something for each Case ID, you'll likely need to use a cursor, and allocate the value for each row to your variable.
If you attempt to assign multiple values to a variable, the variable will be assigned the last value of what ever was returned in that dataset.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply