Join to a table name stored in a field

Question

Join to a table name stored in a field

Daniel-399344

Newbie

Points: 2
More actions
February 22, 2008 at 11:47 am

#62341

Hi, I have multiple websites running off the same server that have a seperate databases for their content and user logins but tracking history is stored in a central database. I would like to query the tracking information for all sites and have user information available.
Central Database
_________
Table: Sites s
Fields: site_id, site_db, site_userdb
Table: Tracking t (join sites s on t.tr_site=s.site_id)
Fields: tr_id, tr_time, tr_type, tr_site, tr_user
Site specific database
___________
UserDB's:
Table: users_*varies* u (join tracking t on u.user_id=t.tr_user)
Fields: user_id, username, company, first_name, last_name (etc)
What I'd like to do is SELECT FROM SITES and JOIN user tables by pulling the database name and table name stored in sites.site_db and sites.site_userdb fields respectively.
So, is it possible to join a table by pulling the table name I want to join out of a text field?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

This could probably be done using dynamic sql. To really help you, we need more info from you to be sure we give you something useful back. Please read the following article, it will provide you with guidance on what we could use to better assist you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

😎

tbeadle SSCommitted Points: 1635 More actions · Answer 2

[font="Arial"]

Hello,

Following the suggestion above....

Say you had a table of server names that you wanted to pull data from.

create table server_I_want

( rowid int identity

server_name varchar(40) null )

Then in a sproc:

declare @select_clause varchar(200),

@from_clause varchar(200),

@where_clause varchar(4000),

@sql_text varchar(4400),

@server_name varchar(40),

@row int,

@rowcount int

set @row = 1

set @rowcount= ( select max(rowId) from server_I_want)

while @row <= @rowcount

begin

select @server_name = ( select server_name from server_I_want where rowId = @row )

set @select_clause = 'select (something(s)) '

set @from_clause = @server_name + '.dbo.' + 'table_name'

set @where_clause = ' where something is something'

set @sql_text = @select_clause + @from_clause + @where_clause

exec dbo.sp_executesql @stmt = @sql_text

set @row = @row + 1

end

This should give you the general idea.

Hope this helps.

Regards,

Terry

[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Actually, for me it isn't much more help. I could really use a more detailed description of what you are trying to accomplish. If you could provide DDL for the tables involved, some sample data, and what you would expect from the query generated, that would help.

😎

Daniel-399344 Newbie Points: 2 More actions · Answer 4

It appears that the answer to my question is that it is not possible to do in a single transaction but instead requires a rather large query to be dynamically generated to make all the "JOIN" statements for each table that needs joined (every user table listed in "Sites") either in a stored procedure or in my application (asp page), which I'm currently doing in the webpage.

Thank you both for taking the time to reply and offer help.