CODEWITHSUNDEEP
sqlms-access
jason m
jason m

Reputation: 6835

alter table with space in name

I have a table with a space in the name generated by a system.

I am trying to alter the table name to remove the space so that it can be processed by a library the pre-exists.

I am trying:

ALTER TABLE 'My Table'
 RENAME TO 'MyTable';

I have also tried double quotes, no luck.

Any pointers?

Upvotes: 3

Views: 7688

Answers (4)

georgios
georgios

Reputation: 502

For MySQL, single quotes, double quotes or brackets did NOT work for me. Only backticks (aka backquotes) worked.

So, try this:

ALTER TABLE `My Table`
 RENAME TO MyTable;

Upvotes: 2

onedaywhen
onedaywhen

Reputation: 57023

This is one of those things that is a lot easier to achieve using the Access GUI!

To do the same in SQL DDL you must 'clone' the table, for which you must already have knowledge of all the attribute names, types, constraints, etc, noting it may have features that are not creatable via SQL DDL e.g. Validation Rules. Then you need to populate it using the original table. The you drop the original. Phew!

Upvotes: 0

Jamie F
Jamie F

Reputation: 23789

[This will not work in MS-Access. Tables cannot be renamed in Access. Not clear if original question applied to MS Access.]

Square brackets:

ALTER TABLE [My Table]
 RENAME TO [MyTable];

Square brackets can't enclose the entire object "path" so this won't work:

ALTER TABLE [MyDatabase.dbo.My Table]

but this will

ALTER TABLE [MyDatabase].[dbo].[My Table]

Upvotes: 9

Mike M.
Mike M.

Reputation: 12511

[My Table]

You can use square brackets in SQL to get around this.

It has many functions, you can use keywords in tables, put spaces and periods in table names or schemas, etc.

For example you can have the schema [Work.Employees]. With the square bracket it would be [Work.Employees].Addresses (schema, table). However, if you forget the brackets it will attempt to find the database Work -> schema Employees -> Table Addresses.

However, it is generally good practice to avoid doing any of the above :)

Upvotes: 0

Related Questions