All for Joomla All for Webmasters

SQL INSERT MULTIPLE ROWS

Many times developers ask that is it possible to insert multiple rows into a single table in a single statement. Currently developers have to write multiple insert statement when they insert values in a table. It is not only boring, also time consuming. To get rid from this you should try this syntax. Actually there are three different methods to insert multiple values into a single table.

  1. Traditional method (simple insert)
  2. SQL insert select
  3. SQL server 2008+ Row Construction

Insert multiple values in SQL server

  1. CREATE TABLE student (ID INT VALUE VARCHAR (100));

1. SQL INSERT: (TRADITIONAL INSERT)

  1. INSERT INTO student (ID, NAME)
  2. VALUES (1, ‘ARMAAN’);
  3. INSERT INTO student (ID, NAME)
  4. VALUES (2, ‘BILLY’);
  5. INSERT INTO student (ID, NAME)
  6. VALUES (3, ‘CHARLIE’);

TO CLEAN-UP:

  1. TRUNCATE TABLE student;

2. INSERT SELECT: (SELECT UNION INSERT)

  1. INSERT INTO student (ID, NAME)
  2. SELECT 1, ‘ARMAAN’
  3. UNION ALL
  4. SELECT 2, ‘BILLY’
  5. UNION ALL
  6. SELECT 3, ‘CHARLIE’;
  7. TO CLEAN-UP:
  8. TRUNCATE TABLE student;

3.SQL Server 2008+ Row Construction

  1. INSERT INTO student (ID, NAME)
  2. VALUES (1, ‘ARMAAN’), (2, ‘BILLY’), (3, ‘CHARLIE’);
  3. TO CLEAN-UP:
  4. DROP TABLE student;

Note:Row Constructor is a new feature for SQL Server 2008. It is not supported by SQL Server 2005.

PinIt
submit to reddit

Leave a Reply

Top