Select a variable table name

Question

I am trying to write some SQL statements for queries for a Microsoft Dynamics Nav database. I am trying to write scripts that i can easily use in different Nav databases. The table names for each database are unique in that they always have the customer's name in the table name followed by a $tablename. So for example if the name of the company is ABC Company the table Rating would be ABC Company$Rating and in another database for the XYZ Company it would be XYZ Company_$Rating. Any Suggestions?

Answer 1

I found one way to do it (though it is really clunky) using dynamic SQL. The downside to this is that it takes a lot of code to write a simple select statement and the context of the script is not checked for errors like a normal script would be:

DECLARE @Company TABLE ( id INT IDENTITY, Name NVARCHAR(30) )

INSERT INTO @Company (Name)
SELECT Name FROM Company

declare @Script varchar(max) = 'select * from [[$Company$]$Customer]'
DECLARE @script2 VARCHAR(MAX)

DECLARE @CompanyCount int = (SELECT COUNT(Name) FROM @Company)
DECLARE @Row INT = 0

DECLARE @CompanyName NVARCHAR(30)

WHILE @Row <= @CompanyCount
BEGIN
SET @Row = @Row + 1
SET @companyName = (SELECT TOP 1 Name FROM @Company WHERE id = @Row)
SET @CompanyName = REPLACE(@CompanyName, '.', '_')
SET @script2 = REPLACE(@script, '[$Company$]', @CompanyName)    
EXEC (@script2)
end 

Answer 2

It's not clear to me why the table names are different since each customers tables seem to be in a different database. If you can avoid naming the tables differently for each customer, I would suggest that.

However, if you must provide these customer-centric table names, perhaps you could also create a set of views for each table with the 'base' table name e.g.

create view Rating as select * from ACME$Rating;

Then write all of your scripts using these views.