MYSQL: Creating Dynamic Table Names 5.1

Applications Web Development
1

Hey everyone. Thanks for looking at this.

I'm trying to create a table with the dynamic name of TableName + today's date.

My variables are all happily created but the system chokes when I try to create the new table name example:

Set @BFBW = CONCAT("BFBW", CURDATE());
Select @BFBW;

Runs well. But when I try to create my new table; bad things happen:

CREATE TABLE (select @BFBW) (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
);

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select @BFBW) (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
)' at line 1

It also doesn't work if I try to use it without the select statement (Of course),

---------- Post updated at 02:36 PM ---------- Previous update was at 02:13 PM ----------

Here's a problem...

[SQL] CREATE TABLE BFBW2011-11-29 (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-11-29 (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
)' at line 1
2

@BFBW is already a value, you don't need select to turn it into one. 'select' just amounts to a 'print' above, you don't need it.

CREATE TABLE @BFBW
...
1 Like
3

Well I removed the hyphens but still get the error:

Set @BFBW = CONCAT("BFBW", DATE_FORMAT(CURDATE(), '%m%d%Y'));
#Select @BFBW;
CREATE TABLE (SELECT @BFBW) (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
);

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT @BFBW) (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
)' at line 2

---------- Post updated at 02:56 PM ---------- Previous update was at 02:55 PM ----------

[SQL] 
Set @BFBW = CONCAT("BFBW", DATE_FORMAT(CURDATE(), '%m%d%Y'));
Affected rows: 0
Time: 0.188ms

[SQL] 
#Select @BFBW;
CREATE TABLE @BFBW (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@BFBW (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
)' at line 2

---------- Post updated at 02:59 PM ---------- Previous update was at 02:56 PM ----------

It just doesn't like the Variable for a table name example:

Set @BFBW = CONCAT("BFBW", "G");
CREATE TABLE @BFBW (
  TenantId LONG 
, PostDate DATETIME
, Credit DOUBLE
);