[SqlServer] SQL copy table definition and copy data rows

Source: Internet
Author: User
1. Copy table definition

Assume that we have a data table "person" with five columns: ID, firstname, lastname, weight, and height. you can refer to this article for the table structure. Now we want to create a new table named "people". The table structure is exactly the same as that of "person", that is, the column names and data types are consistent. We can create this table by copying the structure of the person table rather than copying the records in the table, and paste the SQL code:

1,SQL Server

SQL Server uses a query and into clause with no rows returned:

SELECT    Id,    FirstName,    LastName,    Weight,    Height    INTO PeopleFROM    Person(NOLOCK)WHERE 1=0


SELECT * INTO People FROM Person WHERE 1=0

When you use the into command to repeat tabulation, the rows returned by the query are added to the newly generated table people (for details about copying table data, see section 2 ), unless we define a condition with a constant of false in the WHERE clause condition, that is, 1 = 0.

Note that the ID of the person table is an auto-increment primary key, but the ID of the newly created people table is not changed to the primary key.

2,Oracle, MySql, and PostgreSQL

CREATE TABLE People ASSELECT    Id,    FirstName,    LastName,    Weight,    HeightFROM    PersonWHERE 1=0

The query section and the query conditions are exactly the same as those of SQL Server.



Select is a float cloud, which is so concise. From this statement, we can think that IBM wins over ms, Oracle ....... ?

2. Copy rows from one table to another

For example, if the person and people tables already exist, copy the persons whose IDs are less than 10 in the person table to the people table:

1,The table structure is identical.

INSERT INTO People(    --Id,    FirstName,    LastName,    Weight,    Height)SELECT     --Id,    FirstName,    LastName,    Weight,    HeightFROM    Person    WHERE Id<10

The insert into statement specifies the columns to be inserted. The Select column sequence must be the same as the column sequence written at the insert operation. If no column is specified, all columns are inserted, as shown below:


When I select a primary key, SQL Server prompts "When identity_insert is set to off, an explicit value cannot be inserted for the ID column in the 'others' table ".

2,One fewer column in The People table

Assume that people has one less weight column, use the following SQL statement

INSERT INTO People(    --Id,    FirstName,    LastName,    --Weight,    Height)SELECT    --Id,    FirstName,    LastName,    --Weight,    HeightFROM    Person    WHERE Id<10

The data is inserted normally.

3,One more column in The People table

Suppose there is one more column in The People table, and the height minus the weight is called HWDiff. The height and weight are float type. Now we design HWDiff to be int type, and then execute the copy:

INSERT INTO People(    --Id,    FirstName,    LastName,    Weight,    Height,    HWDiff)SELECT    --Id,    FirstName,    LastName,    Weight,    Height,    Height-Weight AS HWDiffFROM    Person    WHERE Id<10

The test also passed, because the float and int types can be converted to each other (the reason is mutual conversion, you can try to design the Height and Weight of the Person table into the int type, the corresponding columns of the People table can be designed as float columns and then copied to try again.) Of course, it would be better to design HWDiff directly as float columns. From 2 and 3, we can explain that if the columns we insert to the target table are exactly the same as the selected columns, and the table structure is not the same, it can be copied normally.

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.