Join Multiple tables dynamically in sql server

Question

Here is my query. i have two tables as example

In future i'll add more tables in a query.

Then how can i join those tables dynamically in single query

1) tblEmployee

    - EmpId
    - Name
    - DOB

2) tblSalary

    - EmpId
    - TotalSalary
    - PF
    - NetSalary

How can i get the data. table names and field names are in variable with comma separated.

DECLARE @FieldNames VARCHAR(MAX) = 'Id, Name, NetSalary'
DECLARE @TableName VARCHAR(MAX) = 'tblEmployee, tblSalary'

SELECT @FieldNames FROM @TableNames

can i join this tables automatically with primary key and foreign key?

above query returns too many dummy data.

no sir, the above query is return the data but it returns 1 row multiple times. so check it out at least one time. — Darshak Kumbhani, Mar 12 '18 at 08:35
While your doing cross join M*N rows will return and ambiguity columns problem also raise so try to maintain relation between them — santoshkumar Pithani, Mar 12 '18 at 08:47

IngoB · Answer 1 · 2018-03-12T12:47:36.413

0

Though this is a bit too much dynamic in my opinion, you could loop over the table names (split the table names string before), try to get the foreign keys (e. g. like so: How can I list all foreign keys referencing a given table in SQL Server?) loop over them and then build an awful dynamic SQL string with JOINS and execute it with "execute (@cmd)". The "master" table would be the one which doesn't contain FK to all other tables.

edited Mar 12 '18 at 12:47

answered Mar 12 '18 at 08:55

IngoB

2,552
1
20
35

Split the table names string before and loop over the table names. – IngoB Mar 12 '18 at 12:48
Sorry, I have no time for that. You could use a sql cursor to loop over the split table names. See google for "t sql cursor", "t sql split string comma", "t sql dynamic sql" and the JOIN syntax. It's not the most trivial task so you might be better of finding another solution for your problem, not using that much error prone dynamic. – IngoB Mar 13 '18 at 10:26

Join Multiple tables dynamically in sql server

1 Answers1