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

2018-11-02 13:45:00
黄善超
原创
202

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

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



在MySQL数据库中:

方法一:

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

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


在Oracle数据库中:
方法一:
INSERT INTO sc(SID,cid,g) VALUES (2011001,001,90);
INSERT INTO sc(SID,cid,g) VALUES (2011002,001,91);
INSERT INTO sc(SID,cid,g) VALUES (2011003,001,92);
INSERT INTO sc(SID,cid,g) VALUES (2011004,002,93);


方法二:
INSERT INTO sc(SID,cid,g)SELECT 2011001,001,90 FROM dual UNION ALL 
SELECT 2011002,001,91 FROM dual UNION ALL SELECT 2011003,001,92 FROM dual UNION ALL 
SELECT 2011004,002,93 FROM dual UNION ALL SELECT 2011005,002,94 FROM dual UNION ALL 
SELECT 2011006,002,95 FROM dual 


在SQL SERVER中:

方法一:

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



方法二:

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


方法三(MSSQL2008等高版本支持):

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



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


    分享