How do i do this INSERT in Oracle

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How do i do this INSERT in Oracle

Author

Topic

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-01-31 : 08:27:13

Hi All
I am now learning some basic things in Oracle and want to do multiple inserts. In SQL SERVER we can use this, but I want to know how it is done in ORACLE.



INSERT INTO MyNewTable (FName,LName)
SELECT 'Leslie', 'Jones'
UNION 
SELECT 'Steve','Martin'
UNION 
SELECT 'Amber','Maco'

If anyone can provide assistance it will be greatly appreciated.

Dane

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-01-31 : 08:36:08

UNION and UNION ALL is same both in SQL SERVER and Oracle

SQL SERVER Syntax:
SELECT [ALL | DISTINCT] {select_list}
[INTO [owner.]table]
[FROM [owner.]{table | view}[alias] [HOLDLOCK]
[,[owner.]{table | view }[alias]
[HOLDLOCK]]...]
[WHERE condition]
[GROUP BY [ALL] aggregate_free_expression [, aggregate_free_expression]...]
[HAVING search_condition]
[UNION [ALL] SELECT...]
[ORDER BY {[[owner.]{table | view }.]column | select_list_number | expression}
[ASC | DESC]
[,{[[owner.]{table | view }.]column | select_list_number | expression}
[ASC | DESC]...]
[COMPUTE row_aggregate(column)
[,row_aggregate(column)...]
[BY column [, column...]]]
[FOR BROWSE]
The individual element in the select list is as follows:
[alias = ]
{* | [owner.]{table | view}.* | SELECT ... | {[owner.]table.column | constant_literal | expression}
[alias]}

ORACLE Syntax:
SELECT [ALL | DISTINCT] {select_list}
FROM [user.]{table | view } [@dblink] [alias]
[, [user.] {table | view3} [@dblink] [alias]...
[WHERE condition]
[CONNECT BY condition [START WITH condition]]
[GROUP BY aggregate_free_expression
[,aggregate_free_expression]...]
[HAVING search_condition]
[ {UNION [ALL] | INTERSECT | MINUS} SELECT ...]
[ORDER BY {expression | position} [ASC | DESC]...]
[FOR UPDATE [OF [[user.]{table | view}.]column
[,[[user.]{table | view}.]column... ]
[noWAIT] ]
The individual element in the select list is as follows:
{ * | [owner.]{table | view | snapshot | synonym}.* | {[owner.]table.column | constant_literal | expression }
alias]}

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-01-31 : 09:23:06

quote:
Originally posted by Dallr

Hi All
I am now learning some basic things in Oracle and want to do multiple inserts. In SQL SERVER we can use this, but I want to know how it is done in ORACLE.

INSERT INTO MyNewTable (FName,LName)
SELECT 'Leslie', 'Jones'
UNION
SELECT 'Steve','Martin'
UNION
SELECT 'Amber','Maco'

If anyone can provide assistance it will be greatly appreciated.

Dane

try this an let me know if it works,

INSERT INTO MyNewTable (FName,LName)
SELECT 'Leslie', 'Jones' from test
UNION
SELECT 'Steve','Martin' from test
UNION
SELECT 'Amber','Maco' from test

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-01-31 : 15:04:18

Sackets that syntax does not work. PS: Why are using a table Test? I was just trying to insert some values that were not associated with any table.

Dane

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2008-01-31 : 17:13:51

Maybe you should ask in an Oracle forum

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-31 : 21:52:49

In oracle you need a table to select from and the DUAL table serves this purpose (it has only one row)

SELECT 'Steve', 'Martin' from DUAL

Now go to an oracle forum :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-01 : 03:04:13

strange is it ?

a table name DUAL with only one row

KH
[spoiler]Time is always against us[/spoiler]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-02-04 : 06:26:01

its dual.. not test .. am sorry !

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-04 : 20:50:30

quote:
Originally posted by khtan

strange is it ?

a table name DUAL with only one row

KH
[spoiler]Time is always against us[/spoiler]

Yeah - kind of odd. This is why you should try to get stuff right first time or you live with it forever!

[url]http://en.wikipedia.org/wiki/DUAL_table#History[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources