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.
| Author |
Topic |
|
Dan1978
Starting Member
5 Posts |
 Posted - 2012-09-10 : 05:48:34
|
| I have a database that contains a number of individual tables for each sales agents. I need to find a way to set up a query that dynamically unions all tables in the database, so as an new agent joins the business their table will automatically be picked up in the union. The idea being, I have 1 table that contains the information for all agents.Any help would be greatly appreciated.Many thanks in advance.Tables in database ( example)DavidJohnStevenColumns in agents table ( example),[Start_Time],[End_Time],[Record_Type],[User_Action],[Direction],[Action_Data],[From_addr],[To_addr],[Subject],[Detail],[Company],[Contact],[FullPath] |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-10 : 06:55:28
|
| Creating a new table for each agent is not a recommended design. Instead, if you have the opportunity to do so, add another column in the table for the agent name (or even better agent id, with the details such as frist name, last name etc. stored in another table).If you must do it this way, it can be done, but it makes everything more difficult. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-10 : 10:14:04
|
| i would also second Sunita on this. Add a master table for agents and add the key field( agentid) as a foreign key to your table and store values against this. This would give you oppurtunity to store multiple agent data in it rather than using separate tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Union Help