SELECTINTO Statement data table full table copy backup

Source: Internet
Author: User
Selectinto statement: the selectinto statement is usually used to create backup copies of a table or to archive records. Sqlselectinto syntax you can insert all columns into the new table: select * intonew_table_name [inexternaldatabase] fromold_tablename or just insert the expected columns into the new table: selectcol select into Statement data table full table copy backup

The select into statement is often used to create backup copies of a table or archive records.

SQL select into syntax
You can insert all columns into the new table:

Select *
Into new_table_name [in externaldatabase]
From old_tablename
Or insert only the expected columns into the new table:

Select column_name (s)
Into new_table_name [in externaldatabase]
From old_tablename


Instance

The following statement indicates that there is no problem in executing the column across but removing the auto-incrementing Column

Select * into # tbranch from test. dbo. tb_test ts

-- The following statement indicates that the execution in the same database is normal.

Select identity (int, 1, 1) as [newid], * into # tbranch from tb_test


Prepare a backup copy

The following example creates a backup copy for the "persons" table: select * into persons_backupfrom persons

The in clause can be used to copy a table to another database: select persons. * into persons in 'backup. mdb' from persons

If we want to copy some fields, we can list these fields after the select statement: select lastname, firstname into persons_backupfrom persons

You can also add a where clause. The following example extracts the information of the persons living in sandnes from the "persons" table and creates a table named "persons_backup" with two columns: select lastname, firstname into persons_backupfrom personswhere city = 'sandes'

You can also select data from more than one table. The following example creates a new table named "empl_ord_backup", which contains information obtained from the employees and orders tables:


Select employees. name, orders. productinto empl_ord_backupfrom employeesinner join


Summary:
1. What is index: the index in the database is a set of values of one or more columns in a table and the corresponding index

List of logical pointers on the data page that physically identifies these values in the table.
2. Category:
Unique index: two rows cannot have the same index value.

Automatically create a unique index)
Primary Key Index: each value in the primary key must be unique.

Index)
Clustered index: the physical order of each row in the table is the same as the logic (INDEX) Order of key values.

The table can only contain one clustered index. The primary key column is a clustered index by default.
Nonclustered: The physical order of each row in a table and the logical (INDEX) Order of key values

No matching. The table can have 249 non-clustered indexes.
3. Criteria for creating indexes: columns used for frequent searches; columns used for sorting data
Note: if there are only a few rows in the table or the column contains only a few different values, it is not recommended to create an index because

The server takes longer to search data by index in a small table than to search by row.

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.