SQL Server syntax for inserting partial columns in another table and for inserting temporary tables directly using select statements

Source: Internet
Author: User

The syntax is as follows:
1 Description: copy a table (only copy structure, source table name: a new table name: B) (access available)
Method 1: Select * Into B From A Where 1 <> 1
Method 2: Select Top 0 * Into B From A

2Description: Copy table (copy data, source table name: A target table name: B) (access available)
Insert IntoB (A, B, C)SelectD, E, FFromA;

3. Create Table # mytemptable (COLA int primary key)
Insert into # mytemptable values (1)

 

4. Select * into # tmptb from table

 

Release of temporary tables:

Temporary table
You can create local and global temporary tables. The local temporary table is only visible in the current session; the global temporary table is visible in all sessions.

The name of the local temporary table is preceded by a number character (# table_name), and the name of the global temporary table is preceded by two numbers (# table_name ).

The SQL statement uses the name specified for table_name In the CREATE TABLE statement to reference the temporary table:

Create Table # mytemptable (COLA int primary key)
Insert into # mytemptable values (1)

If a local temporary table is created by a stored procedure or is executed by multiple users at the same timeProgramSQL Server must be able to differentiate the tables created by different users. Therefore, SQL Server adds a digital suffix to the table name of each local temporary table internally. The full name of a temporary table stored in the sysobjects table of the tempdb database is composed of the table name specified in the create table statement and the digital suffix generated by the system. To allow append suffixes, the table name table_name specified for the local temporary table cannot exceed 116 characters.

Unless the drop TABLE statement is used to explicitly remove a temporary table, the system automatically removes the temporary table when it exits its scope:

When the stored procedure is complete, the local temporary table created in the stored procedure is automatically removed. All nested stored procedures executed by the stored procedure of the created table can reference this table. However, the process that calls the stored procedure to create this table cannot reference this table.

All other local temporary tables are automatically removed at the end of the current session.

the global temporary table is automatically removed when the session for this table is created and other tasks are stopped. The association between tasks and tables is only maintained during the lifecycle of a single Transact-SQL statement. In other words, when the session for creating a global temporary table ends, the table is automatically removed after the last Transact-SQL statement that references the table is completed.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.