Search the Site

Sponsors

bottom corner

Inserting multiple records at once into a database table

This note provides a sample for how to insert multiple records into a database table with only one insert statement. This provides a more efficient way to insert data into a table than calling multiple statements.

This page is filed under keyword(s): mysql, oracle, sqlserver.

For demonstration purposes, we are going to assume that we have a table named "employees" with two fields, name and department.

Oracle

insert all
  into employees (name, department) values('Aaron','Accounting')
  into employees (name, department) values('Ibis','IT')
  into employees (name, department) values('Mary','Marketing')
  into employees (name, department) values('Paul','Useless')
select '' from dual;

MySQL, PostgreSQL, and SQL Server

insert into employees (name, department) values
  ('Aaron','Accounting'),
  ('Ibis','IT'),
  ('Mary','Marketing'),
  ('Paul','Useless');

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): mysql, oracle, sqlserver.
Author: C. Peter Chen
Last updated: 14 Aug 2009

bottom corner