Access在查詢中使用 Insert into語句一次插入多條記録

2018-11-02 13:45:00
tmtony8
原創
6142

在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; 


    分享