locked
Table data from multiple databases RRS feed

  • Question

  • I have multiple databases on the same SQL Server. I have a database for each customer (so multiple customer databases) and 1 central database. In the central database I have a table that stores info about the customers including the name of their database and a customer id. The customer databases are pretty much the same (some might be a version or 2 behind but not for long) and I can deal with the fact that some tables might changes for compatibility so it is not a problem.

    I am trying to create a view that will contain the info from a table from every customer database. Let's say I have a table called Invoice in every customer database (same table name, same columns) and I want to create a view in my central database that will retrieve the invoices of all customers. What I am doing currently is that I created in the central database an Invoice table that is the same as the Invoice table in the customer databases but I added a column for the customer Id. Then every morning I have a stored procedure that runs dynamic SQL to copy the new data from the Invoice table of every customer database to the central database (using the table that lists the customers to retrieve the list of databases). It is long and the same data is stored at 2 places. So what I am trying to do is use view that would retrieve the data directly form the customer database instead of making a copy.

    I am trying to find the best practice here and avoid dynamic SQL if possible (I don't think it is). So far what I managed to do is write a stored procedure that is called in the trigger of the customer table so that when a customer is added/removed, this stored procedure is called and will dynamically update the view that lists all the invoices. I have to hardcode the customer databases name, I don't think I can use the name stored in the customer table? Is that right? I don't really like the idea of a stored procedure modying a view (so this stored procedure will create a view that looks smilar to select * from CustomerA.Invoice union select * from CustomerB.Invoice union ...). Another solution I can think of is having a stored procedure return the Invoices directly using dynamic SQL (so no database modification) instead of using a view.

    Is there a "best practice" for what I am trying to do? I assumed that other dev would need to "consolidate" data from multiple databases as I do but I don't seem to find any info. I use SQL Server 2008 standard.

    Thanks!

    • Moved by Tom Phillips Thursday, August 18, 2011 9:13 PM TSQL question (From:SQL Server Database Engine)
    Thursday, August 18, 2011 6:10 PM

Answers

  • Generally you will have to do some type of dynamic SQL to make this manageable, and the way I have always done it was to have a stored procedure that would generate and execute dynamic SQL to refresh the views definition based on the customer database names contained in the centralized databases table.  Then you use the view for general purposes, and when a new customer is added, you execute the stored procedure to update the view definition to include that customers database in the definition.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, August 18, 2011 7:24 PM

All replies

  • Generally you will have to do some type of dynamic SQL to make this manageable, and the way I have always done it was to have a stored procedure that would generate and execute dynamic SQL to refresh the views definition based on the customer database names contained in the centralized databases table.  Then you use the view for general purposes, and when a new customer is added, you execute the stored procedure to update the view definition to include that customers database in the definition.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    Thursday, August 18, 2011 7:24 PM
  • Thanks Jonathan, that is basically what I am working on so it seems that I am on the right track. I was wondering if it was the best practice but it looks like it is.
    Thursday, August 18, 2011 7:44 PM