Usage and differences of SELECT INTO and insert INTO select

The main difference between a SELECT INTO and an INSERT into select is to copy a table, and the primary distinction is that the target table does not exist because it is created automatically when it is inserted. Insert into select from requires the target table to existHere are the syntax for each of them INSERT into SELECT statement The statement form is: Insert into Table2 (field1,field2,...) Select Value1,value2,... from Table1 Attention Place: (1) requires that the target table Table2 must exist, and the field field,field2 ... must also exist (2) Note the PRIMARY KEY constraint for Table2, if Table2 has a primary key and is not empty, then field1, field2 ... Must include the primary key (3) Note the syntax, do not add values, and insert a data SQL mixed, do not write: INSERT INTO Table2 (field1,field2,...) VALUES (select Value1,value2,... From Table1) (4) Because the target table Table2 already exists, we can insert constants in addition to the fields Table1 the source table. Complete Example:SQL CodeCopy

    --1. Creating a Test table

    Create TABLE Table1

    (

         A varchar (10),

         b varchar (10),

         C varchar (10),

         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. Creating test data

    Insert into Table1 values (' Zhao ', ' ASDs ', ' 90 ')

    Insert into Table1 values (' money ', ' ASDs ', ' 100 ')

    Insert into Table1 values (' Sun ', ' ASDs ', ' 80 ')

    Insert into Table1 values (' Lee ', ' ASDs ', null)

    GO

    SELECT * FROM Table2

    --3.insert into SELECT statement to copy table data

    Insert into Table2 (A, C, D) select a,c,5 from Table1

    GO

    --4. Displaying the updated results

    SELECT * FROM Table2

    GO

    --5. Deleting a test table

    Drop TABLE Table1

    Drop TABLE Table2

SELECT into from statement

The statement form is: SELECT vale1, value2 into Table2 from Table1 requires that the target table Table2 not exist because the table Table2 is automatically created on insert and the specified field data in Table1 is copied to Table2. Complete Example:SQL CodeCopy

    --1. Creating a Test table

     Create TABLE Table1

    (

         A varchar (10),

         b varchar (10),

         C varchar (10),

         CONSTRAINT [Pk_table1] PRIMARY KEY CLUSTERED

         (

             A ASC

         )

    ) on [PRIMARY]

    GO

    --2. Creating test data

     Insert into Table1 values (' Zhao ', ' ASDs ', ' 90 ')

    Insert into Table1 values (' money ', ' ASDs ', ' 100 ')

    Insert into Table1 values (' Sun ', ' ASDs ', ' 80 ')

    Insert into Table1 values (' Lee ', ' ASDs ', null)

    GO

    --3.select to from statement CREATE table Table2 and copy data

     Select A,c to Table2 from Table1

    GO

    --4. Displaying the updated results

     SELECT * FROM Table2

    GO

    --5. Deleting a test table

     Drop TABLE Table1

    Drop TABLE Table2

Usage and differences of SELECT INTO and insert INTO select