Select into and insert into select copy SQL statements to the entire table

Insert is a common statement in T-SQL. Insert INTO table (field1, field2 ,...) values (value1, value2 ,...) this form is essential for application development. However, during the development and testing processes, we often encounter situations where table replication is required, such as copying some of the data fields of Table 1 to Table 2 or copying the entire table 1 to Table 2, in this case, we need to use the select into and insert into select table copy statements.

Differences

The code is as follows:	Copy code
Select * into destTbl from srcTbl Insert into destTbl (fld1, fld2) select fld1, 5 from srcTbl

The preceding two statements insert the srcTbl data to destTbl, but the two statements are different.

The first clause (select into from) requires that the target table (destTbl) does not exist because it is automatically created during insertion.

The second sentence (insert into select from) requires that the target table (destTbl) exists. Because the target table already exists, we can insert constants in addition to the fields in the source table (srcTbl).

1. insert into select statement

Statement format: Insert into Table2 (field1, field2,...) select value1, value2,... from Table1

The target table Table2 must exist. Because the target table Table2 already exists, we can insert constants in addition to the fields in the source table Table1. Example:

The code is as follows:	Copy code
Create proc getBusinessLineHistory AS BEGIN SELECT * FROM sys. databases END GO   Sp_configure 'show Advanced options', 1 GO RECONFIGURE GO Sp_configure 'ad Hoc Distributed Queries ', 1 GO RECONFIGURE GO   SELECT * INTO # MyTempTable from openrowset ('sqlncl', 'server = (local) SQL2008; Trusted_Connection = yes ;', 'Exec getBusinessLineHistory ')   SELECT * FROM # MyTempTable

Insert into select statement to copy table data

2. select into from statement

Statement format: SELECT vale1, value2 into Table2 from Table1

The target Table 2 does not exist because TABLE 2 is automatically created during insertion and the specified field data in Table 1 is copied to Table 2. Example:

Select into from: create a table and copy table data

The code is as follows:

Copy code

-- 1. Create 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. Create test data Insert into Table1 values ('Zhao ', 'asds', '90 ') Insert into Table1 values ('Money', 'asds ', '123 ') Insert into Table1 values ('sun', 'asds ', '80 ') Insert into Table1 values ('Lil', 'asds ', null) GO -- 3. select into from Statement to create table 2 and copy data Select a, c INTO Table2 from Table1 GO -- 4. Display the updated result Select * from Table2 GO -- 5. Delete the Test table Drop TABLE Table1 Drop TABLE Table2

Note:

An error occurs when no records are returned in the table. If multiple records are returned in the table, an error occurs. Use exception protection.

The code is as follows:	Copy code
Declare Jobid integer; Begin Begin Select job into jobid from all_jobs where what = 'testjob '; Exception When others then Jobid: = ''; -- specify a value. End; End;

There is no problem in assigning values to multiple variables at the same time:

Select Field 1, field 2, Field 3 into variable 1, variable 2, variable 3 from table name where...