sql插入多条记录_如何在SQL中插入多条记录

小咪咪 2023-02-28 06:14 20阅读 0赞

sql插入多条记录

Hey, folks! In this article we will be focusing on ways to **Insert Multiple rows in SQL**.

嘿伙计！ 在本文中，我们将重点介绍**在SQL中插入多行的**方法。

## 需要SQL插入INTO多行查询 **(****Need of SQL Insert INTO Multiple rows query****)** ##

[SQL INSERT query][] inserts data into the columns of a particular table.

[SQL INSERT查询][SQL INSERT query]将数据插入到特定表的列中。

The normal SQL INSERT query inputs the data values in a single row. In case when we want to insert data in multiple rows at once, this query fails.

普通SQL INSERT查询在一行中输入数据值。 如果我们要一次在多行中插入数据，此查询将失败。

Thus, in order to save the execution time, we need to use the SQL INSERT query in such a manner that it injects data into multiple rows at once.

因此，为了节省执行时间，我们需要使用SQL INSERT查询，以使其一次将数据注入到多行中。

Having understood the need of SQL Insert query for multiple rows, let us get started with the implementation of the same.

了解了多行SQL插入查询的需求之后，让我们开始执行相同的操作。

--------------------

## 传统SQL INSERT查询可插入多个记录 **(****Traditional SQL INSERT query to insert multiple records****)** ##

Traditional SQL INSERT query injects input data into multiple rows. In this technique, we need to the insert query as many times we want to input data into the rows of the table.

传统SQL INSERT查询将输入数据注入多行。 在这种技术中，我们需要向表行中输入数据的次数要多次插入查询。

The basic drawback of this query is the overhead of execution of every insert query for multiple rows injection.

该查询的基本缺点是执行多行注入的每个插入查询的开销。

**Example:**

**例：**

create table Info(id integer, Cost integer, city varchar(200));
    insert into Info(id, Cost,city) values(1, 100,"Pune");
    insert into Info(id, Cost,city) values(2, 50, "Satara");
    insert into Info(id, Cost,city) values(3, 65,"Pune");
    insert into Info(id, Cost,city) values(4, 97,"Mumbai");
    insert into Info(id, Cost,city) values(5, 12,"USA");
    select * from Info;

**Output:**

**输出：**

1	100	Pune
    2	50	Satara
    3	65	Pune
    4	97	Mumbai
    5	12	USA

--------------------

## INSERT-SELECT-UNION查询以插入多个记录 **(****INSERT-SELECT-UNION query to insert multiple records****)** ##

In the above section, we got to know that INSERT INTO query injects multiple records. But, if we observer the output of it, we get to know that the clause ‘INSERT INTO’ is repeated many times.

在上一节中，我们了解了INSERT INTO查询将注入多条记录。 但是，如果我们观察它的输出，就会知道“ INSERT INTO”子句被重复了很多次。

Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table.

因此，我们可以使用INSERT-SELECT-UNION查询将数据插入表的多行中。

The [SQL UNION query][] helps to select all the data that has been enclosed by the [SELECT query][] through the INSERT statement.

[SQL UNION查询][SQL UNION query]有助于通过INSERT语句[选择SELECT查询][SELECT query]所包含的所有数据。

create table Info(id integer, Cost integer);
    INSERT INTO Info (id, Cost)  
    SELECT 1, '123'  
    UNION ALL   
    SELECT 2, '234'  
    UNION ALL  
    SELECT 3, '456';  
    
    select * from Info;

**Output:**

**输出：**

1	123
    2	234
    3	456

--------------------

## 行构造以插入多个记录 **(****Row construction to insert multiple records****)** ##

The SQL INSERT query is used in a manner wherein we make use of a single INSERT query to insert multiple records within a single point of execution.

使用SQL INSERT查询的方式是，我们利用单个INSERT查询在单个执行点内插入多个记录。

**Syntax:**

**句法：**

INSERT INTO Table (columns)  
    VALUES (val1, val2, valN);

**Example:**

**例：**

create table Info(id integer, Cost integer,city nvarchar(200));
    INSERT INTO Info (id,Cost,city)  
    VALUES (1,200, 'Pune'), (2, 150,'USA'), (3,345, 'France');  
    
    select * from Info;

**Output:**

**输出：**

1	200	Pune
    2	150	USA
    3	345	France

--------------------

## 结论 **(****Conclusion****)** ##

By this we have come to the end of this topic. Herein we have covered three different techniques to INSERT data values across multiple records of a table.

至此，我们到了本主题的结尾。 本文中，我们介绍了三种不同的技术，可跨表的多个记录插入数据值。

Please feel free to comment in case you come across any doubt.

如果您有任何疑问，请随时发表评论。

For more such posts related to SQL, please visit [SQL JournalDev][].

有关与SQL有关的更多此类帖子，请访问[SQL JournalDev][] 。

--------------------

## 参考资料 **(****References****)** ##

*  [SQL Insert into multiple rows — StackOverFlow][SQL Insert into multiple rows _ StackOverFlow]
    
    [SQL插入多行中-StackOverFlow][SQL Insert into multiple rows _ StackOverFlow]

> 翻译自: [https://www.journaldev.com/40783/sql-insert-multiple-rows][https_www.journaldev.com_40783_sql-insert-multiple-rows]

sql插入多条记录

[SQL INSERT query]: https://www.journaldev.com/18270/sql-insert-query
[SQL UNION query]: https://www.journaldev.com/23804/sql-union-all
[SELECT query]: https://www.journaldev.com/18262/sql-select
[SQL JournalDev]: https://www.journaldev.com/sql
[SQL Insert into multiple rows _ StackOverFlow]: https://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query
[https_www.journaldev.com_40783_sql-insert-multiple-rows]: https://www.journaldev.com/40783/sql-insert-multiple-rows