Multiple SQL inserts: Oracle, Mysql, Mssql
Found a useful technique for inserting multiple rows into Oracle, MYSQL and MS SQL databases.
Mysql supports this:VALUES
('Value1','Fernando'),
('Value2','Carlos'),
('Value3','Vincent')
select ('Value1','Fernando') from dual
union all
select ('Value2','Carlos') from dual
union all
select ('Value3','Vincent') from dual
DUAL is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record.
In MS SQL the union all technique also works:select ('Value1','Fernando')
UNION ALL
select ('Value2','Carlos')
UNION ALL
select ('Value3','Vincent')
This technique is probably a lot faster than using multiple inserts in coldfusion and certainly helps reduce the round-trips that would need to be done.
Sponsored Links
Archives By Subject
BlogCFC (2)Coldfusion (18)
CSS (4)
Design (5)
Javascript (1)
Life (7)
PhotoShop (5)
SQL (4)
Technology (5)
The Web (7)
Calendar
| M | T | W | T | F | S | S |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | |||
| 5 | 6 | 7 | 8 | 9 | 10 | 11 |
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
| 26 | 27 | 28 | 29 | 30 | 31 |
Recent Comments
Multiple SQL inserts: Oracle, Mysql, MssqlJAYESH TALSANIYA said: in mssql u cannot use parentheses in select statement. use as below: INSERT INTO [TABLE] (column1,... [More]
Multiple SQL inserts: Oracle, Mysql, MssqlJD said: I found with MSSQL, I couldn't use parentheses in my select statements for insert. So, from your ex... [More]
BlogCFC ping list and timeoutsWill Wilson said: Nice :) that will come in handy, thanks! [More]
Is this a Coldfusion 8 BUGBarb said: I'm having the same problem . . . when I start CF8, it seems to hang during or just after the user M... [More]
Is this a Coldfusion 8 BUGJoão Fernandes said: Phil, when you install CF8 they give you an option to install LCDS within CF. The messageBrokerServl... [More]
Bookmarks
Russ Michaels blogw3csites.com
riaforge.org
smashingmagazine.com
coldfusioncommunity.org
succor.co.uk
cssplay.co.uk
hotukdeals.com
feedicons.com
coldfusionjedi.com
alistapart.com
photoshopcandy.com
Recent Entries
Where is my iPhone - o2 poor servicCheapest iPhone on the internet
Is this a Coldfusion 8 BUG
Online CSS Layouts designer
Create 3D-Box Images for Your Web A
Thanks for that - really helped save my pages from timing out
I found with MSSQL, I couldn't use parentheses in my select statements for insert. So, from your example, I had to do: select 'value1','Fernando' union all select 'value2','Carlos' etc. Thanks for the reference though!
in mssql u cannot use parentheses in select statement. use as below: INSERT INTO [TABLE] (column1,column2) select 'Value1','Fernando' UNION ALL select 'Value2','Carlos' UNION ALL select 'Value3','Vincent'