[SqlServer] SQL copy table definition and copy data rows

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

Equivalent

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.

3,DB2

CREATE TABLE People LIKE Person

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:

INSERT INTO People SELECT * FROM Person WHERE Id<10

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.