How to dynamically select a db based on data in another db's column
-
So here is my layout.
I've got identical customer DB's (different data)
DBName Table Columns
customerDB1.table1.client_id(client1234), hair_color(brown), age(34)
customerDB2.table1.client_id(client999) , hair_color(red), age(50)
customerDB3.table1.client_id(client45897), hair_color(blonde), age(42)
And I have another db that services all of the above
DBName Table
serviceDB.table1
this table has a column of type string that contains the name of the customerDB
and another column of the client_id of that corresponds to the client_db of the customer db
so it looks like
customerDB1,client1234
customerDB3,client45897
So what I want to do is join the record from my serviceDB table to the correct customerDB
The result would look like
customerDB1,client1234,brown, 34
customerDB3,client45897, blonde, 42
Make Sense?
-
Well you description is rather confusing but the only way you are going to pull something like this off is with dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Here's a picture that I hope unmuddies it a little bit.
-
Dynamic SQL is the only way to alter your target 'on the fly'.
In a case like this however you might be better off just setting up a massive union to those databases and pulling the data over and then using your local table as part of the join components.
There's no easy way to do this... which is why it's typically avoided like the plague.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
That design is pure evil! Dynamic SQL has it limits in terms of how much you should do before the codebase becomes unmaintainable...and I fear you would be pressing to find those limits trying to do all this work in T-SQL. Moderation is key when it comes to dynamic SQL. You may be better off coding a data access layer in an application language.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
I agree with the previous two...this is a recipe for disaster. I have worked on a system that was like this and it was pure and absolute hell. The code is a nightmare to deal with and performance is all over the place. If it is not too late, go back to the drawing board and rethink this. You would be far better off with a multi-tenant type of approach. This means you have all the data for each customer in a single database and you use composite keys all over the place.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I appreciate the warnings, but altering the layout is not an option. This is what I've been given to work with. I'm working on a dynamic solution right now.
-
I can help you get started if you can post some ddl, sample data and desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply