SQL code SQL Replication data table and table structure

Select *  into  from source table name Insert  into Select 5  from source table name

The above two sentences are to insert the ' source table ' data into the ' target table ', but the two sentences are different:

The first sentence (select to from) requires that the target table does not exist because it is created automatically when it is inserted.

The second sentence (insert to select from) requires that the target table exist, because the target table already exists, so we can insert a constant in addition to the field of the source table, as in the example: 5.

Example

Currently, the following data are available:

Now to copy the data with ID 2 into the table, use the following statement:

Insert  into Select *  from where id=2;

Results

Now to copy the data with ID 2 into the table, and change the value of its name to ' F ', use the following statement:

Insert  into Select ID,'f'fromwhere id=2

Results

For this, we can expand the following, there are currently the following data:

1: Copy table structure and data to a new table

SELECT *  into  from Original table name

We copy the structure and data of the table test to the new table test1:

SELECT *  into  from Test

The results are as follows:

2. Back up part of the table column (do not write * and write out the list of columns)

SELECT  into  from the original table name

We copy the data from the table test1 column id,name to the test2:

SELECT  into  from Test1

The results are as follows:

3. Back up part of the table row (plus WHERE condition)

SELECT *  into  from WHERE Condition

We copy the data with ID 1 from test2 to Test3:

SELECT *  into  from WHERE = 1

The results are as follows:

4. Back up a portion of the table (do not write * and write out a list of columns) and some rows (plus where condition)

SELECT  into  from WHERE Condition

We copy the data from the column id,age with the data table Test1 to test4 at ID greater than 0 o'clock:

SELECT  into  from WHERE > 0

The results are as follows:

5. Copy only the structure of the table

In this case, we just set the query condition is not valid. We copy the table structure of the table test1 to the new table Test5:

SELECT *  into  from WHERE 1 = 2

The results are as follows:

6. Replicated structure data from multiple tables

In fact, we want multiple table query structure as a data source on the line, we first look at the table test2 and test4 current results and data:

Then I copy the Id,name,age data under the same ID to the new table Test6:

SELECT  into  from (SELECTfromINNERJOINon= as a

The results are as follows:

SQL code SQL Replication data table and table structure