在查询中使用 Insert into语句一次插入多条记录

在Access查询中，我们只能运行一条SQL语句，插入一条记录。

Access是否能通过 Insert into语句一次插入多条记录呢？

在SQL SERVER中：

方法一：

1. 
   
2. INSERT INTO MyTable(ID,NAME) VALUES(1,'123');
   
3. INSERT INTO MyTable(ID,NAME) VALUES(2,'456');
   
4. INSERT INTO MyTable(ID,NAME) VALUES(3,'789')

复制代码

方法二：

1. INSERT INTO MyTable(ID,NAME)
   
2. SELECT 4,'000'
   
3. UNION ALL
   
4. SELECT 5,'001'
   
5. UNION ALL
   
6. SELECT 6,'002'

复制代码

方法三（MSSQL2008等高版本支持）：

1. INSERT INTO MyTable(ID,NAME)
   
2. VALUES(7,'003'),(8,'004'),(9,'005')

复制代码

在Accesss中：

1. Insert INTO 目标表(id,供应商名称,供应商地址)
   
2. Select DISTINCTROW *
   
3. FROM (Select 1 AS ID,"天鸣1" AS 供应商名称,"广东中山" AS 供应商地址  FROM MSysObjects UNION
   
4. Select 2 AS ID,"天鸣2" AS 供应商名称,"广东中山" AS 供应商地址 FROM MSysObjects UNION
   
5. Select 3 AS ID,"天鸣3" AS 供应商名称,"广东中山" AS 供应商地址 FROM MSysObjects UNION
   
6. Select 4 AS ID,"天鸣4" AS 供应商名称,"广东中山" AS 供应商地址 FROM MSysObjects)  AS tblTemp;

复制代码

在MySQL数据库中：

方法一：

1. INSERT INTO STUDENT (SNAME, SSEX, SAGE, SDEPT) SELECT '武耀旭','男',24,'CS' UNION SELECT '张志鹏','男',24,'CS' UNION SELECT '朱文辉','男',24,'CS'

复制代码

方法二：

1. INSERT DELAYED INTO `newsort` (`sort_id`, `sort_name`, `sort_bz`) VALUES(1, 'aas', 'sdsdfsdfsf'),(2, 'ffff', 'gggg'),(4, '生活类', '要懂得生活才好！'),(5, '股票行情', ''),(6, '政治类新闻', ''),(7, 'IT行业新闻', ''),(8, '宗教类新闻', '');

复制代码

在Oracle数据库中：
方法一：

1. INSERT INTO sc(SID,cid,g) VALUES (2011001,001,90);
   
2. INSERT INTO sc(SID,cid,g) VALUES (2011002,001,91);
   
3. INSERT INTO sc(SID,cid,g) VALUES (2011003,001,92);
   
4. INSERT INTO sc(SID,cid,g) VALUES (2011004,002,93);

复制代码

方法二：

1. INSERT INTO sc(SID,cid,g)SELECT 2011001,001,90 FROM dual UNION ALL
   
2. SELECT 2011002,001,91 FROM dual UNION ALL SELECT 2011003,001,92 FROM dual UNION ALL
   
3. SELECT 2011004,002,93 FROM dual UNION ALL SELECT 2011005,002,94 FROM dual UNION ALL
   
4. SELECT 2011006,002,95 FROM dual

复制代码