February 6, 2017 at 3:16 pm
Greetings,
i have a challenge, who can point me or show me the simplest how to for retrieving data from multiple databases.
e.g:
SELECT m.name AS Market,ISNULL(usr.first_name,'')+','+ISNULL(usr.last_name,'') AS AE, SUM(od.rate) AS Revenue
FROM .order_header oh
INNER JOIN .order_detail od ON oh.id = od.order_header_id
INNER JOIN .order_spot os ON od.id = os.order_detail_id
INNER JOIN .log ON os.id=log.order_spot_ID
INNER JOIN .users usr ON usr.id=oh.user_id
INNER JOIN .market m ON oh.market_id = m.id
WHERE (log.log_date BETWEEN getdate() AND dateadd(day,-7,getdate())
AND (od.revenue_type_id IN (@RTID))
GROUP BY m.name,ISNULL(usr.first_name,'')+','+ISNULL(usr.last_name,'')
ORDER BY Market,AE
how can i process this script to run across all the databases and store the results from all the databases in a temp table.
please assist.
I learn from the footprints of giants......
February 6, 2017 at 3:42 pm
JALLY - Monday, February 6, 2017 3:16 PMGreetings,i have a challenge, who can point me or show me the simplest how to for retrieving data from multiple databases.
e.g:
SELECT m.name AS Market,ISNULL(usr.first_name,'')+','+ISNULL(usr.last_name,'') AS AE, SUM(od.rate) AS Revenue
FROM .order_header oh
INNER JOIN .order_detail od ON oh.id = od.order_header_id
INNER JOIN .order_spot os ON od.id = os.order_detail_id
INNER JOIN .log ON os.id=log.order_spot_ID
INNER JOIN .users usr ON usr.id=oh.user_id
INNER JOIN .market m ON oh.market_id = m.id
WHERE (log.log_date BETWEEN getdate() AND dateadd(day,-7,getdate())
AND (od.revenue_type_id IN (@RTID))
GROUP BY m.name,ISNULL(usr.first_name,'')+','+ISNULL(usr.last_name,'')
ORDER BY Market,AEhow can i process this script to run across all the databases and store the results from all the databases in a temp table.
please assist.
Here's one way that will validate and report on the outcome for each database.
1. Create a control table with the list of databases you want to play the code against.
2. Change the in the code above to an INSERT/SELECT where the target of the INSERT is the Temp Table.
3. Once step 2 is complete, change the code to dynamic SQL that will include a USE statement that materializes a database name as a part of the USE statement.
4. Add some code at the beginning that validates that the database name in the variable exists in the control table to add an additional layer of protection against SQL Injection and to force people to use the control table.
5. Wrap all of that as a stored procedure.
6. Write another stored procedure or script that reads the rows from the control table, creates the Temp Table, and executes the stored procedure above once for each row.
Another way would be to dynamically create a view that uses 3 part naming conventions and UNION ALL. If the view creation were written as a stored procedure, you'd just run the proc to create the view from the control table if you ever needed to add a database to the mix. I'm not sure if there's still a limit of 253 UNION ALLs in such a view or not.
p.s. Both solution above assume that all the databases are on the same instance.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 6, 2017 at 4:31 pm
Try:
EXEC sys.sp_MSforeachdb @command1 = N'USE ?',
@replacechar = N'?',
@command2 = N'IF DB_NAME() IN (''db1'', ''db2'')
INSERT tempdb.dbo.acumulation_table SELECT m.name AS Market,ISNULL(usr.first_name,'')+','+ISNULL(usr.last_name,'') AS AE, SUM(od.rate) AS Revenue
FROM .order_header oh
INNER JOIN .order_detail od ON oh.id = od.order_header_id
INNER JOIN .order_spot os ON od.id = os.order_detail_id
INNER JOIN .log ON os.id=log.order_spot_ID
INNER JOIN .users usr ON usr.id=oh.user_id
INNER JOIN .market m ON oh.market_id = m.id
WHERE (log.log_date BETWEEN getdate() AND dateadd(day,-7,getdate())
AND (od.revenue_type_id IN (@RTID))
GROUP BY m.name,ISNULL(usr.first_name,'')+','+ISNULL(usr.last_name,'')
ORDER BY Market,AE' ;
Replace IF DB_NAME() IN (''db1'', ''db2'') with the databases you want to run against and accumulation_table with a name of your choice for the table in which you want to combine the data.
February 7, 2017 at 9:55 am
Thank you for your responses everyone.
this is what i cam e up with. i passed the database name into a variable , now i am trying to use that variable as a 3 part naming convention as part of where the table will pull data from buy i keep getting errors :
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '+@DB_NAME+'.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'GROUP'.
Msg 102, Level 15, State 1, Line 15
see my script below:
IF OBJECT_ID ('tempdb..#REVENUEBYMARKET') IS NOT NULL
DROP TABLE #REVENUEBYMARKET
CREATE TABLE #REVENUEBYMARKET
(MARKET NVARCHAR (200),AE NVARCHAR (100),REVENUE INT)
DECLARE @db_name NVARCHAR (150)
DECLARE C_DB_NAMES CURSOR FOR
SELECT NAME FROM sys.sysdatabases WHERE NAME NOT IN ('MASTER','TEMPDB','YIELDMANAGEMENT','TRAFFIC')
OPEN C_DB_NAMES;
FETCH C_DB_NAMES INTO @DB_NAME
WHILE @@FETCH_STATUS=0
BEGIN
EXEC ('
DECLARE @STARTDATE DATETIME=GETDATE()
DECLARE @ENDDATE DATETIME =DATEADD(DAY,-7,@STARTDATE)
DECLARE @IDTABLE TABLE
(ID INT, DESCRIPTION NVARCHAR (100))
INSERT INTO @IDTABLE
select distinct ID as value, [DESCRIPTION] AS [DESCRIPTION] from .dbo.revenue_type order by id
INSERT Into #REVENUEBYMARKET
SELECT m.name AS Market,ISNULL(usr.first_name,'''')+'',''+ISNULL(usr.last_name,'''') AS AE, SUM(od.rate) AS Revenue
FROM .order_header oh
INNER JOIN ''+@DB_NAME+''.dbo.order_detail od ON oh.id = od.order_header_id
INNER JOIN ''+@DB_NAME+''.dbo.order_spot os ON od.id = os.order_detail_id
INNER JOIN ''+@DB_NAME+''.dbo.log ON os.id=log.order_spot_ID
INNER JOIN ''+@DB_NAME+''.dbo.users usr ON usr.id=oh.user_id
INNER JOIN ''+@DB_NAME+''.dbo.market m ON oh.market_id = m.id
WHERE log.log_date BETWEEN @StartDate AND @EndDate
AND od.revenue_type_id IN( Select ID from @IDTABLE)
GROUP BY m.name,ISNULL(usr.first_name,'''')+'',''+ISNULL(usr.last_name,'''')
ORDER BY Market,AE')
FETCH C_DB_NAMES INTO @DB_NAME
END
CLOSE C_DB_NAMES;
DEALLOCATE C_DB_NAMES;
Select * from #REVENUEBYMARKET
I learn from the footprints of giants......
February 7, 2017 at 10:33 am
Looks like just a few syntax issues. Try these changes...
--Check for Object existence
IF (object_id('''+@DB_NAME+'.dbo.revenue_type'') is not null)
BEGIN
INSERT INTO @IDTABLE
select distinct ID as value, [DESCRIPTION] AS [DESCRIPTION]
from '+@DB_NAME+'.dbo.revenue_type order by id
INSERT Into #REVENUEBYMARKET
SELECT m.name AS Market,ISNULL(usr.first_name,'''')+'',''+ISNULL(usr.last_name,'''') AS AE, SUM(od.rate) AS Revenue
FROM .order_header oh
INNER JOIN '+@DB_NAME+'.dbo.order_detail od ON oh.id = od.order_header_id
INNER JOIN '+@DB_NAME+'.dbo.order_spot os ON od.id = os.order_detail_id
INNER JOIN '+@DB_NAME+'.dbo.log ON os.id=log.order_spot_ID
INNER JOIN '+@DB_NAME+'.dbo.users usr ON usr.id=oh.user_id
INNER JOIN '+@DB_NAME+'.dbo.market m ON oh.market_id = m.id
WHERE log.log_date BETWEEN @StartDate AND @EndDate
AND od.revenue_type_id IN( Select ID from @IDTABLE)
GROUP BY m.name,ISNULL(usr.first_name,'''')+'',''+ISNULL(usr.last_name,'''')
ORDER BY Market,AE
END
')
February 7, 2017 at 10:55 am
mjbentley - Tuesday, February 7, 2017 10:33 AMLooks like just a few syntax issues. Try these changes...
--Check for Object existence
IF (object_id('''+@DB_NAME+'.dbo.revenue_type'') is not null)
BEGININSERT INTO @IDTABLE
select distinct ID as value, [DESCRIPTION] AS [DESCRIPTION]
from '+@DB_NAME+'.dbo.revenue_type order by idINSERT Into #REVENUEBYMARKET
SELECT m.name AS Market,ISNULL(usr.first_name,'''')+'',''+ISNULL(usr.last_name,'''') AS AE, SUM(od.rate) AS Revenue
FROM .order_header oh
INNER JOIN '+@DB_NAME+'.dbo.order_detail od ON oh.id = od.order_header_id
INNER JOIN '+@DB_NAME+'.dbo.order_spot os ON od.id = os.order_detail_id
INNER JOIN '+@DB_NAME+'.dbo.log ON os.id=log.order_spot_ID
INNER JOIN '+@DB_NAME+'.dbo.users usr ON usr.id=oh.user_id
INNER JOIN '+@DB_NAME+'.dbo.market m ON oh.market_id = m.id
WHERE log.log_date BETWEEN @StartDate AND @EndDate
AND od.revenue_type_id IN( Select ID from @IDTABLE)
GROUP BY m.name,ISNULL(usr.first_name,'''')+'',''+ISNULL(usr.last_name,'''')
ORDER BY Market,AEEND
')
Thank you for your reply,
you solution above will not work though.
you used single quotes for variable concatenation, i dont think an ix exits is required for a table variable as it only executes within the batch of a begin an end statement as it is not stored as an object in the database. i tried it and it did not work. how best can one include a databse name as a variable to be used in a 3 part name convention?
thanks
I learn from the footprints of giants......
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply