Inserting more than one data at a time in SQL

Inserts in SQL can insert one piece of data at a time, and we have three ways to insert multiple data at once.

1.

Syntax: Select field List into new table from source table

Note: This new table is automatically created by the system, no new tables can exist until the statement is executed, and the new table retains only the identity column attribute of the source table, and the other constraints cannot be persisted.

If you only need the data structure of the source table, we can add it in the statement (top 0)

2.

Syntax: INSERT INTO Destination table Select field List from source table

Note: The destination table of this method must exist before the statement executes, and each column should correspond to the source table.

Here are some interesting questions, when I use the following code to insert multiple data:

Select Top 0 *  into  from   studentinsertintoselect* from student

This error can occur in this case:

Because ClassID is an identity column in the Newclass table, we cannot insert a value.

Our solution is as follows:

Select Top 0 *  into  from   studentsetidentity_inserton insert  Select* from student

We inserted the Newstudent's identity column into the display, and added the column Name field to insert.

We will then create a new NEWCLASS2:

Select Top 0 *  into  from MyClass Set Identity_insert  on Insert  into Select *  from MyClass

This is also an error, because we have previously set Newclass's identity column insert as on, we must first close before we can insert a value to Newclass2, the code is as follows:

Select Top 0 *  into  from MyClass Set Identity_insert off Set Identity_insert  on Insert  into Select *  from MyClass

So far we've solved the use of the second method to insert multiple data at a time.

3.

Syntax: INSERT into table (Field column name) Select Value Union Select value

Inserting more than one data at a time in SQL