Backing up tables using "SELECT * INTO MY_BACKUP_DATABASE..CustomersTemporaryTable FROM Custome-CodePudding

I am using SQL Server. I found the following way to backup a database table:

-- Taking a backup    
SELECT * INTO MY_BACKUP_DATABASE..CustomersTemporaryTable FROM Customers

I am trying to understand the .. in the syntax. From what I understand, the sentence means that Customers is the table that is going to be backed-up by placing it all of its content into the database called MY_BACKUP_DATABASE using CustomersTemporaryTable as the destination table. I assume when executing the sentence, CustomersTemporaryTable must already exist. Is my understanding of the sentence to take a backup correct?

CodePudding user response：

Each MS SQL Table identifiers can have a name compound of three parts separates with a dot :

the database name
the SQL schema name (by default dbo)
the table, view or Table UDF name

Syntax :

db_name.schema_name.table_name

But it is not always necessary to specify the three parts.

Inside the current database, no need to specify the db_name. It's implicit... By default every SQL user is associate with a specific default schema (frequently dbo too...).

So you can specify a table name with :

schema_name.table_name

...SQL Server will try to find the table into the current DB

db_name..table_name

...SQL Server will try to find the table into the specified DB and the default user schema

table_name

...SQL Server will try to find the table into the current DB and the default user schema

To know with SQL schema is associated with your SQL user, use :

SELECT SCHEMA_NAME() AS DEFAULT_CURRENT_USER_SCHEMA

To know all the associations between SQL users and SQL schemas, do :

SELECT name AS USER_NAME, default_schema_name
FROM   sys.database_principals
WHERE  type_desc LIKE '%?_USER' ESCAPE '?'

CodePudding user response：

First of all, understand that what you are doing is not "taking a backup", it is inserting data into a table from another table. If you have not created the destination table the syntax is like this:

Select * INTO Destination_Table FROM Source_Table

The destination table will be created automatically. This doesn't necessarily work so well if you will be inserting additional data that might be different lengths or data types, but for a one of select should work fine.