MySQL与Oracle如何insert into多个values
一、Oracle一次插入多条记录
方式1:
要向数据库中写入多条记录,记录集已经确定,如果一次一次插入势必影响效率。
解决方法:
注意:上面最后一个select之后没有union all,否则报错。
方式2:
*************************************************************************************
二、MySQL中可以使用如下格式的插入语句:
insert into persons (id_p, lastname , firstName, city ) values (200,'haha' , 'deng' , 'shenzhen'), (201,'haha2' , 'deng' , 'GD'), (202,'haha3' , 'deng' , 'Beijing');
这样就批量插入数据了。
*****************************************************************************************************************
*****************************************************************************************************************
附录:
--Oracle插入多条记录到一个表中:
INSERT ALL
INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;
--Oracle插入多条记录到多个表中:
INSERT ALL
INTO product (product_id,product_name) VALUES (1000, 'Disc')
INTO product (product_id, product_name) VALUES (2000, 'Floppy')
INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York')
SELECT * FROM dual;
-----------------------------------------------------------------------------------------------------------------------------------------
--Oracle插入多条记录到一个表中:
INSERT INTO tablename (col1,col2,...)
SELECT * FROM (
SELECT 'a1','a2',... FROM dual UNION ALL
SELECT 'a11','a22',... FROM dual UNION ALL
SELECT 'a111','a222',... FROM dual UNION ALL
SELECT 'a1111','a2222',... FROM dual
)
-----------------------------------------------------------------------------------------------------------------------------------------