CODEWITHSUNDEEP
sql-serverdatabase
Kivak Wolf
Kivak Wolf

Reputation: 870

SQL How to SELECT specific fields from tables using a table of table names

SO,

I am trying to find a (messy?) solution to an even more messy problem. I have a SQL Server 2014 database which, in part, stores data from another software package but also stores data for me. The software creates a table with specific fields for each set of data - a Name and a Geometry field. For example, one might contain cities (dtCitiesData), another contains roads (dtRoadsData), another contains states(dtStates), etc. I also have a table (dtSpatialDataTables) which stores the names of the tables which store the data I want. That table only has 2 fields: ID and TableName.

I would like to create a SELECT statement which queries dtSpatialDataTables for all entries, then queries all tables with the name corresponding to each TableName result, and SELECTs Name and Geometry from them.

In pseudocode, effectively I want to do this:

SELECT TableName FROM dtSpatialDataTables

FOREACH TableName :
    SELECT Name, Geometry FROM (TableName)

I can do this easily PHP via a first query against dtSpatialDataTables and then a loop of queries to each of the returned row TableNames but I want to know if this is possible via SQL directly.

In reality, what I want to do is create a VIEW with this query so I can directly query the VIEW rather than soak of processing time on potentially lots of queries.

Is this possible? Unfortunately, my Google-ing doesn't turn up any meaningful results.

Thanks everyone!

PS: I figure this is messy and not the way this should be done. But I have no choice in how the software puts data in my database. I simply have to use what I get. So... whether this is the "right" way or the "wrong" way, I need a solution. :)

Upvotes: 1

Views: 824

Answers (1)

JamieD77
JamieD77

Reputation: 13949

you could do something like this using dynamic sql..

CREATE PROCEDURE dbo.usp_SpatialData_GetByID
(
    @ID INT
)   
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX),
            @Selects NVARCHAR(MAX) = 'SELECT Name, Geometry, ''<<TableName>>'' AS Source FROM <<TableName>>'

    SELECT  @SQL = COALESCE(@SQL + ' UNION ALL ', '') + REPLACE(@Selects, '<<TableName>>', TableName)
    FROM    dtSpatialDataTables
    WHERE   ID = @ID

    EXEC(@SQL)
END
GO

I feel like you left out filtering of the Geometry tables somewhere so you might have to add a filter to the @Selects statement

Upvotes: 1

Related Questions