I have multiple set of data to insert at once
INSERT INTO MyTable VALUES ("John", "Doe", 1234567890, "employee", "");
INSERT INTO MyTable VALUES ("Susen", "Gupta", 1234567890, "leander");
INSERT INTO MyTable VALUES ("Karn", "Share", 1234567890, "employee", "home");
I want to insert multiple rows in a single SQL statement. And can it possible to do it with different number of values.
Multi-row insert has been part of the SQL standard since SQL-92, and many of the modern DBMS' support it. That would allow you to do something like:
insert into MyTable ( Name, Id, Location)
values ('John', 123, 'Lloyds Office'),
('Jane', 124, 'Lloyds Office'),
('Billy', 125, 'London Office'),
('Miranda', 126, 'Bristol Office');
You'll notice I'm using the full form of insert into there, listing the columns to use. I prefer that since it makes you immune from whatever order the columns default to.
If your particular DBMS does not support it, you could do it as part of a transaction which depends on the DBMS but basically looks like:
begin transaction;
insert into MyTable (Name,Id,Location) values ('John',123,'Lloyds Office');
insert into MyTable (Name,Id,Location) values ('Jane',124,'Lloyds Office'),
insert into MyTable (Name,Id,Location) values ('Billy',125,'London Office'),
insert into MyTable (Name,Id,Location) values ('Miranda',126,'Bristol Office');
commit transaction;
This makes the operation atomic, either inserting all values or inserting none.
Yes you can, but it depends on the SQL taste that you are using :) , for example in mysql, and sqlserver:
INSERT INTO Table ( col1, col2 ) VALUES
( val1_1, val1_2 ), ( val2_1, val2_2 ), ( val3_1, val3_2 );
But in oracle:
INSERT ALL
INTO Table (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO Table (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
INTO Table (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
.
.
.
SELECT 1 FROM DUAL;
In SQL Server, you can do this:
INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"),
("Jane", 124, "Lloyds Office"),
("Billy", 125, "London Office"),
("Miranda", 126, "Bristol Office")
