Insert is a commonly used statement in T-sql. Insert INTO table (field 1, field 2,...) values (value 1, value 2,...) is an indispensable form in application development. However, in the process of development and testing, we often encounter the need for table replication, such as copying part of a table1 data field to table2, or copying the entire table1 to table2. At this time, we need to use SELECT INTO and INSERT. INTO SELECT table replication statement.

INSERT INTO SELECT statement

The form of the statement is: Insert into Table2 (field 1, field 2,...) select value1, value2,... From Table1

Table2 is required to exist, and since Table2 already exists, we can insert constants in addition to the fields of Table1 in the source table. Examples are as follows:

 --1.Create test tables
    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.Create test data
    Insert into Table1 values('Zhao','asds','90')
    Insert into Table1 values('money','asds','100')
    Insert into Table1 values('Grandchildren','asds','80')
    Insert into Table1 values('plum','asds',null)
    GO
    select * from Table2

    --3.INSERT INTO SELECT Statement replication table data
    Insert into Table2(a, c, d) select a,c,5 from Table1
    GO

    --4.Display updated results
    select * from Table2
    GO
    --5.Delete test tables
    drop TABLE Table1
    drop TABLE Table2

SELECT INTO FROM statement

Statement form: SELECT vale1, value2 into Table2 from Table1

Table2 does not exist because Table2 is created automatically when inserting and the specified field data in Table1 is copied to Table2. Examples are as follows:

--1.Create test tables
    create TABLE Table1
    (
        a varchar(10),
        b varchar(10),
        c varchar(10),
        CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
        (
            a ASC
        )
    ) ON [PRIMARY]
    GO

    --2.Create test data
    Insert into Table1 values('Zhao','asds','90')
    Insert into Table1 values('money','asds','100')
    Insert into Table1 values('Grandchildren','asds','80')
    Insert into Table1 values('plum','asds',null)
    GO

    --3.SELECT INTO FROM Statement creation table Table2 And copy the data
    select a,c INTO Table2 from Table1
    GO

    --4.Display updated results
    select * from Table2
    GO
    --5.Delete test tables
    drop TABLE Table1
    drop TABLE Table2