在单个SQL查询中插入多行数据

技术背景

在数据库操作中，有时需要一次性向表中插入多行数据。不同的数据库系统提供了不同的方法来实现这一需求，合理使用这些方法可以提高数据插入的效率。

实现步骤

SQL Server 2008及以上版本

在SQL Server 2008中，可以使用单个INSERT语句插入多行数据，语法如下：

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

示例：

INSERT INTO MyTable
  ( Column1, Column2, Column3 )
VALUES
  ('John', 123, 'Lloyds Office'), 
  ('Jane', 124, 'Lloyds Office'), 
  ('Billy', 125, 'London Office'),
  ('Miranda', 126, 'Bristol Office');

不过，这种语法最多只能插入1000行数据。如果需要插入超过1000行数据，可以使用SELECT中的派生表来解决，示例如下：

INSERT INTO MyTable ( Column1, Column2, Column3 )
SELECT V.Column1,
       V.Column2,
       V.Column3
FROM (VALUES('John', 123, 'Lloyds Office'), 
            ('Jane', 124, 'Lloyds Office'), 
            ('Billy', 125, 'London Office'),
            ...
            ('Sally', 10026, 'Bristol Office'))V(Column1, Column2, Column3);

MySQL

在MySQL中，如果要插入到单个表中，可以这样写查询语句：

INSERT INTO table1 (First, Last)
VALUES
    ('Fred', 'Smith'),
    ('John', 'Smith'),
    ('Michael', 'Smith'),
    ('Robert', 'Smith');

SQL Server 2005及小数据集

可以使用INSERT with SELECT UNION ALL方法，示例如下：

INSERT INTO MyTable  (FirstCol, SecondCol)
    SELECT  'First' ,1
    UNION ALL
SELECT  'Second' ,2
    UNION ALL
SELECT  'Third' ,3
...

这种方法适用于小数据集。

核心代码

通用的多行插入（适用于支持VALUES语法的数据库）

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

最佳实践

• 对于数据量较小（少于1000行）的情况，优先使用VALUES语法，因为它简洁明了。
• 当数据量较大时，在SQL Server中使用SELECT中的派生表，避免1000行的限制。
• 根据不同的数据库系统选择合适的方法，以提高插入效率。

常见问题

• 插入行数限制：如在SQL Server中使用VALUES语法有1000行的限制，需要使用派生表的方法来解决。
• 兼容性问题：不同的数据库系统支持的语法可能不同，需要根据具体的数据库版本选择合适的方法。