Nightly Batch of Data from Multiple Databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nightly Batch of Data from Multiple Databases

Author

Topic

FooYay
Starting Member

2 Posts

Posted - 2012-01-27 : 15:01:45

Hey there! So I've got a problem I can't wrap my head around, I've been given a request to populate customer data in a table in one of our databases. Now the data needs to come from several other databases.

So my financials database (FIN_DB) contains a table called CUSTOMER_USAGE that has these columns:

Customer
SHORTNAME
DBASE
RM_Users
RO_Users
DM_Users
FileCount
FileSize
DBSize

The DBASE column contains the name of that customers database. I need to populate the remaining data from the other databases. If I pull it manually I can get the user count like this:

Select Count(*) as "Number of Users" from CUST1_DB.customer1.KUAF where id in (select childid from customer1.kuafchildren where id = (Select id from CUST1_DB.customer1.kuaf where name = 'SHORTNAME Users'))

That returns the users, and I put the last where statement as 'SHORTNAME Users' because the SHORTNAME from the above table would match this for that customers database. This would be repeated as SHORTNAME DM, SHORTNAME RM, and SHORTNAME RO for each of the user types.

Here is how I pull the file count and file size:

select count(distinct docid) as "Number of Documents",sum((datasize)/1048576) as "Total Size(MB)" from CUST1_DB.customer1.dversdata

Now I am supposed to populate these fields in the above table from all the customer databases and make sure it includes any future databases that get added to that table. Every night they want the data copied into another table (CUSTOMER_USAGE_HISTORY) to keep historical data, and have the data in the CUSTOMER_USAGE table replaced with the current numbers.

I am at a complete loss for this....I have tried writing even queries to pull from multiple databases with no luck, much less any idea how to make this an automatic process. Any advice or assistance would be GREATLY appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 17:37:42

for the first part you can do like this


Select Count(case when k1.name = 'SHORTNAME Users' then 1 end) as SHORTNAME,
Count(case when k1.name = 'SHORTNAME DM' then 1 end) as DM_Users,
Count(case when k1.name = 'SHORTNAME RM' then 1 end) as RM_Users,
Count(case when k1.name = 'SHORTNAME RO' then 1 end) as RO_Users
from CUST1_DB.customer1.KUAF k1
inner join customer1.kuafchildren k2
ON k2.id = k1.id
inner join CUST1_DB.customer1.KUAF k3
on k3.id = k2.childid

for second art i didnt get how you connect it to CUST1_DB.customer1.dversdata

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 18:01:24

>> Hey there! So I've got a problem I can't wrap my head around, I've been given a request to populate customer data in a table in one of our databases. Now the data needs to come from several other databases.

OK

Let's go Slowly

1. IS this SQL Server and what version
2. The table that needs to be populate (customer data), can you give us the DDL
3. The database the data needs to come from. Are they on the same server? On the same instance? Got the DDL for those?
4. And Finally, do you have Rules (In Business terms, not pseudo code) that will be required to populate the destination table?

Read the hint link in my sig

BUT my guess is..you need an algorithm (rules) that say which data is the "best" to populate the destination table.

If I'm RIGHT..tell us what those rules are

i.e. Dtabase 123 is the social security database and that wins if it's ther
Database 456 is the next best...

yada, yada, yada...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

FooYay
Starting Member

2 Posts

Posted - 2012-01-29 : 22:56:37

1. Yes it is running on SQL Server 2008 R2

2. Table DDL for table needing to be populated:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CUSTOMER_USAGE](
	[Customer] [nvarchar](max) NOT NULL,
	[SHORTNAME] [nvarchar](max) NOT NULL,
	[AdGroup] [nvarchar](max) NULL,
	[DBASE] [nvarchar](max) NULL,
	[RM_Users] [nvarchar](max) NULL,
	[RO_Users] [nvarchar](max) NULL,
	[DM_Users] [nvarchar](max) NULL,
	[File Count] [nvarchar](max) NULL,
	[File Size] [nvarchar](max) NULL
	[DB Size] [nvarchar](max) NULL
) ON [PRIMARY]
GO

3. All of the databases are on the same server and instance of SQL Server 2008 R2. The databases and tables are all the same format, large tables (the DBs are part of an off the shelf content management system). The DDL's for these would be rather large but I can post if they will be helpful/needed.

4. There aren't really any business rules, as they are all the same type of DB with the same type of content, the table has to be populated with the same data from each.

If it helps at all, I will try to explain again also with example data:

[Customer] contains the full name of our customer such as "WACO"
[SHORTNAME] contains a shortned name that is used to identify the customers library in this case again "WACO"
[AdGroup] Contains the ActiveDirectory master group for customers, not important to me.
[DBASE] Contains the name for the DB in SQL Server 2008 R2 for that customers library, in this case again ECM_CM_WACO
[RM_Users] This field needs to be populated with the results of a query, preferably referencing the SHORTNAME above and the database name from the DBASE column, but the query for my "WACO" customer would be:

Select Count(*) as "Number of Users"  from ECM_CM_WACO.ECM_CM_WACO.KUAF where id in (select childid from ECM_CM_WACO.ECM_CM.WACO.kuafchildren where id = (Select id from ECM_CM_WACO.ECM_CM.WACO.kuaf where name = 'WACO RM Users'))

[RO_Users] Same as above, needs to be populated from the query below, just changes the RM to RO:

Select Count(*) as "Number of Users"  from ECM_CM_WACO.ECM_CM_WACO.KUAF where id in (select childid from ECM_CM_WACO.ECM_CM.WACO.kuafchildren where id = (Select id from ECM_CM_WACO.ECM_CM.WACO.kuaf where name = 'WACO RO Users'))

[DM_Users] Same as above, needs to be populated from the query below, just changes the RO to DM:

Select Count(*) as "Number of Users"  from ECM_CM_WACO.ECM_CM_WACO.KUAF where id in (select childid from ECM_CM_WACO.ECM_CM.WACO.kuafchildren where id = (Select id from ECM_CM_WACO.ECM_CM.WACO.kuaf where name = 'WACO DM Users'))

[File Count] Needs to be populated, once again using the DBASE column to would be preferable but for my WACO customer it would be:
select count(distinct docid) as "Number of Documents" from ECM_CM_WACO.dversdata
[File Size] Same as above but filesize instead of file count:
select sum((datasize)/1048576) as "Total Size(MB)" from ECM_CM_WACO.dversdata
[DB Size] Needs to be populated to show the disk size of the database for that customer (ie in this case the ECM_CM_WACO database)

Since I am an involuntary DBA (SA left after staff cuts) and am being pressured to make this happen by month end, I appreciate any and all help you can provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-30 : 10:59:25

you still havent explained much on ECM_CM_WACO.dversdata
which all columns it has for relating to CUSTOMER_USAGE table?
does it have shortname?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources