I know the eternal war between DBA’s and Developers rages on :D, While most developers prefer to make multiple inserts. DBA’s always use UNION all. While highly debatable which is fast performance wise. But if you are doing thousands of insert’s UNION ALL is definitely faster. I have narrowed down inserts from 40 second to milliseconds using UNION ALL.
How to do UNION ALL
- Consider a table MyTable with two columns : FirstCol, SecondCol
- Normal insert on this table you would do as follows
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Second’,2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Third’,3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Fourth’,4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘Fifth’,5);
COMMIT;
The clause INSERT INTO is repeated multiple times. Many times we copy and paste to save time. Now let’s do it the alternative way
- UNION ALL alternative method
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
COMMIT;
If you are doing more than 1K inserts, then this is the simplest way. Anything above 10K inserts should use BULK INSERT method or Open Cursors in a loop.
SimpleSQLDBA | Shadab Mohammad 