office交流網--QQ交流群號

Access培訓群:792054000         Excel免費交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

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

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

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


    分享