0

My table name is dynamic, they are something like "PM1234", whereas "PM" is hard coded, and "1234" comes from a column of another table, say select max(col1) from table1. Is there any way to put PM and 1234 together as table name?

New data will be inserted into "table1", so the data will be increased, this time "max(col1)" is "1234", next time it will be "1235".

Here's what I tried:

SELECT *
FROM (SELECT 'PM'+ CAST(MAX(col1) AS NVARCHAR)
      FROM table1)

but it doesn't work.

I just figured out how to make it work:

DECLARE @sql varchar(200)
DECLARE @ID varchar(200)

SELECT @ID = MAX(ID) FROM table1 

SET @sql = 'SELECT * FROM PM' + @ID

EXEC(@sql)
lemon
  • 14,875
  • 6
  • 18
  • 38
Joe Zhu
  • 11
  • 1
  • 3
    Yip, you can do that with dynamic SQL... there are loads of resources available using your favourite search engine. – Dale K May 26 '23 at 02:38
  • 1
    What is the reason for "dynamic tables"? They usually cause more pain than gain. – Alex May 26 '23 at 02:43
  • 3
    *"My table name is dynamic"* this sounds like a design flaw. – Thom A May 26 '23 at 02:45
  • 2
    Seems to be a common (bad) design pattern these days, create tables on demand instead of partitioning or similar. – Dale K May 26 '23 at 02:47
  • 1
    Take off your solution from your post. Post it as an answer, in the section below. Then [accept it](https://meta.stackexchange.com/a/5235) as the solving solution for your problem. @JoeZhu – lemon Jun 04 '23 at 23:42

0 Answers0