Is it possible to insert more than one row in a table with one insert statement? I know this will happen if I do:
insert into table ( fields ) select values from another_table
But what if I want to insert:
row 1 - ( a1, b1, c1 )
row 2 - ( a2, b2, c2 )
...
row n - ( an, bn, cn )
with just one insert command?
Two solutions (source : http://appsfr.free.fr/spip.php?article21 ):
INSERT ALL
INTO table (column1, column2)
VALUES (value1, value2)
INTO table (column1, column2)
VALUES (value1, value2)
...etc...
SELECT * FROM DUAL ;
or
INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL ;
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;
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
INSERT INTO College (CustomerID, FirstName, MiddleName, LastName)
SELECT 7, N'Charles', N'Simmons', N'Burns'
UNION ALL
SELECT 9, N'Dominic', N'Fred', N'Einsten'
UNION ALL
SELECT 12, N'Dave', N'William, N'Bryan';
NOTE: Letter N before each hard coded string value converts string to an NVARCHAR value to match the datatype of the column.
No, this is not possible. As you already stated yourself, it is only possible with a select clause providing the insert values and rows.
