Why can't I "SELECT INTO" a database whose name starts with a number?

Question

Main.dbName = "7202" 

query = "select * into " + Main.dbName + ".dbo.[AccountReceivableHistory]
        from " + dbOrigin + ".dbo.[AccountReceivableHistory] where
        AccountReceivableHistory].Date >= '2012-12-27' and    
        AccountReceivableHistory].Date < '2012-12-28'"

The error says

Syntax error near '7202'.

Prefixing a `schema.table` combination with another identifier serves as a database name. You have a database called `7202`? — StuartLC, Jun 02 '15 at 04:33
Starting database object names using numbers is bad practice. You are forcing quotation by that using "" or []. Why not put a simple prefix to it like TBL_7202? — wumpz, Jun 02 '15 at 05:04

score 9 · Accepted Answer · edited May 23 '17 at 12:30

You can use a number for a table name (or database name, or schema name - see my comment), however, you will need to escape it with []:

select *
into [123].[456].[789] 
from OtherTable;

Where [123] represents database, [456] represents schema and [789] represents a table.

Edit

In the interests of standardisation, e.g. if there is a chance of future portability between RDBMS, note that preference should be given to use double quotes " instead of [] for escaping, e.g.

SELECT * from "123"."456"."789";

You will however need to ensure that set QUOTED_IDENTIFIER ON is set on the connection.

If the names of objects are being constructed dynamically, note that it is also possible to override the default escaping of the QUOTENAME function by providing a character delimiter as the second parameter, e.g.:

select QUOTENAME('123', '"');

Why can't I "SELECT INTO" a database whose name starts with a number?

1 Answers1