Variable Table Name in SQL Server

Question

I've looked through the forums and know this question has been asked before, but even after trying what has been suggested I still can't get this to work. I'm fairly new to both VBA and SQL so that could explain why I'm struggling.

What I'm trying to do is create a Form that when executed runs this query:

strSQL = "SELECT new.[Service Name], new.WBS, new.[Billing Code], new.[CPU Units], new.[VCPU Units], new.[Billing Status], new.[Bill To] " & _
 " FROM " & strNew & " AS new LEFT JOIN " & strOld & " AS old ON new.[Service Name] = old.[Service Name] " & _
 " WHERE old.[Service Name] Is Null;"

I created the form and have stored the user inputs into both strNew and strOld respectively. However when I execute this code, I receive an error. I've confirmed the values inputted into the form are actual "tables" that are located in the database. Why does this not work? I appreciate the help.

Try putting blank space before "FROM" and "WHERE". If that doesn't work, edit your post and tell us what error message you are getting. — Tab Alleman
– Tab Alleman, Commented Jul 14, 2015 at 15:16
If you have multiple tables with exactly the same columns (i.e. it won't cause any errors to chop and change the table names in this query) then this is a bit of a code smell. Could you not have a single table, then use a column as an identifier where you are currently using a new table? Also consider not putting spaces and other special characters in object names, it is very annoying for anyone who has to query your database! — GarethD
– GarethD, Commented Jul 14, 2015 at 15:36
Urm, what? Unless I am really misreading, are you trying to do a left join on t1.a = t2.a, where t2.a is null? You should be aware that under ANSI (read: sane) rules, nothing is equal to a null - not even another null. So even if you fix the syntax of the query, its sense will still be way off. And even if you worked under relaxed (read: bad) rules regarding comparing nulls, the join would be pointless; you would (for whatever unclear reason) simply need to do a where on each table.column is null. — underscore_d
– underscore_d, Commented Jul 14, 2015 at 20:12

chancrovsky · Accepted Answer · 2015-07-14 15:28:46Z

1

I think you are trying to create a dynamic query, the first advise i can give you, you need to read this Erland Sommarskog's article. In order to solve your problem is:

Dim strNew As String
Dim strOld As String

strSQL = "SELECT new.[Service Name], new.WBS, new.[Billing Code], new.[CPU Units], new.[VCPU Units], new.[Billing Status], new.[Bill To]" & _
"FROM " & strNew & " AS new LEFT JOIN " & strOld & " AS old ON new.[Service Name] = old.[Service Name]" & _
"WHERE old.[Service Name] Is Null;"

Let us know if this code works for you.

answered Jul 14, 2015 at 15:28

chancrovsky

5803 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

GarethD Over a year ago

I'd say it is probably worth enclosing the variable table names in brackets too, e.g. "FROM [" & strNew & "] AS new LEFT JOIN [" & strOld & "] AS old - Given that the column names all contain spaces, it is reasonable to assume that the tables are named just as badly!

Paolo Over a year ago

the spaces before FROM and WHERE are still missing ^^

GarethD Over a year ago

@Paolo Although this is not bad advice, it is not the source of the error, because immediately before the FROM and WHERE there are object names in brackets (e.g. new.[Bill To]FROM ), the query would still run. As an example the following still works correctly -------- SELECT t.[name]FROM sys.tables AS t INNER JOIN sys.schemas AS s ON s.[schema_id] = t.[schema_id]WHERE 1 = 1;

Jack Over a year ago

Thanks for the suggestions. After trying your code Chancrovsky, I received an error message "3131" "Syntax error in FROM clause",

chancrovsky Over a year ago

As Paolo said before, the space before FROM and WHERE are missing... i think this solve that problem: Dim strNew As String Dim strOld As String strSQL = "SELECT new.[Service Name], new.WBS, new.[Billing Code], new.[CPU Units], new.[VCPU Units], new.[Billing Status], new.[Bill To] " & _ "FROM " & strNew & " AS new LEFT JOIN " & strOld & " AS old ON new.[Service Name] = old.[Service Name] " & _ "WHERE old.[Service Name] Is Null;"

|

Collectives™ on Stack Overflow

Variable Table Name in SQL Server

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related