In MySQL select INTO and INSERT into select two table copy statements

Source: Internet
Author: User

Select INTO and INSERT into select two table copy statements
Insert is a common statement in T-SQL, insert into table (Field1,field2,...) VALUES (value1,value2,...)
This form is essential in application development. But in the development, testing process, we often encounter the need for table replication,
If you copy part of a table1 data to a table2, or copy the entire table1 to table2, we
You will use the SELECT INTO and INSERT into the Select table to copy the statement.

1.INSERT into SELECT statement
Statement form: Insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1
The target table Table2 must exist, because the target table Table2 already exists, so in addition to inserting fields from the source table Table1,
You can also insert constants. Examples are as follows:
--1. Create a test table

The code is as follows Copy Code

Create TABLE Table1
    (
        a varchar (a),
        b varchar (a),
        C varchar (
        CONSTRAINT [pk_table1] PRIMARY KEY CLUSTERED
         (
            a ASC
       )
   ) on [PRIMARY]

Create TABLE Table2
(
A varchar (10),
C varchar (10),
d int,
CONSTRAINT [Pk_table2] PRIMARY KEY CLUSTERED
(
A ASC
)
) on [PRIMARY]
Go

--2. Create test data

The code is as follows Copy Code

Insert into Table1 values (' Zhao ', ' ASDs ', ', ')
    Insert into Table1 values (' money ', ' ASDs ', ')
     INSERT into Table1 values (' Sun ', ' ASDs ', ', ')
    insert into Table1 values (' Lee ', ' ASDs ', null)
    Go
    select * to Table2

   --3.insert into Select statement Copy table Data
    Insert into Table2 (A, C, D) Select a,c,5 from Table1
    go

   --4. Show updated Results
    select * from Table2
    go
    --5. Delete Test table
    drop table Table1
    drop table Table2


2.SELECT into from statement
Statement form: SELECT vale1, value2 into Table2 from Table1
The target table Table2 is not present because the table Table2 is created automatically when inserting.
and copies the specified field data in the Table1 to the Table2. Example below

--1. Create a test table

  code is as follows copy code

Create TABLE Table1
    (
        a varchar (),
  & nbsp;     b varchar (a),
        C varchar (a),
        CONSTRAINT [pk_table1] PRIMARY KEY CLUSTERED
         (
            a ASC
        )
   ) on [PRIMARY]
    go

  & nbsp.

--2. Create test data
   

  code is as follows copy code

INSERT into Table1 values (' Zhao ', ' ASDs ', ', ')
    insert INTO Table1 values (' money ', ' ASDs ', ' m ')
    Insert into Table1 values (' Sun ', ' ASDs ', ')
     Insert into Table1 values (' Lee ', ' ASDs ', null)
    go

   --3.select into The FROM statement creates the table Table2 and copies the data
    select A,c into Table2 from Table1
    go

 & nbsp; --4. Show updated Results
    select * from Table2
    go
   -- 5. Delete Test table
    drop table Table1
    drop table Table2

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.