Home » SQL & PL/SQL » SQL & PL/SQL » insert statements
insert statements [message #266630] |
Tue, 11 September 2007 04:39  |
gstijl
Messages: 3 Registered: September 2007
|
Junior Member |
|
|
I created a txt.file with multiple insert commands
insert into table values (value1)
insert into table values (value2)
insert into table values (......)
insert into table values (valuex)
The seperate insert statement are errorfree.
When I start via sql-plus the txt.file an error message appears:
sql-command not properly ended a line 1.
Even when I put an ; at every end the same error appears.
How can I start my file so I can start all the insert statements with one command?
Rgds,
Gerry
|
|
|
|
|
Re: insert statements [message #266647 is a reply to message #266631] |
Tue, 11 September 2007 05:23   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
Arju,
sorry but this makes no sense. (And Yes, I am well aware of INSERT ALL statement) but simply answering with insert all is pointless.
My 2c
|
|
|
Re: insert statements [message #266653 is a reply to message #266630] |
Tue, 11 September 2007 05:36   |
Arju
Messages: 1554 Registered: June 2007 Location: Dhaka,Bangladesh. Mobile:...
|
Senior Member |
 
|
|
No Pablolee, you are not right. We should not give full answer.We should give Just a hint and the questioner should search based on that.He may use google by searching "insert all oracle" keyword.
We are not dedicated to help.
After that he may get this info about insert all.Like,
INSERT ALL
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date, sales_sun)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)
INTO sales (prod_id, cust_id, time_id, amount)
VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)
SELECT product_id, customer_id, weekly_start_date, sales_sun,
sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat
FROM sales_input_table;
A little more we can proceed by providing link like,
url
|
|
|
Re: insert statements [message #266671 is a reply to message #266653] |
Tue, 11 September 2007 06:40   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
I strongly disagree with you Arju. The level of the OPs knowledge appears to be relatively basic. Simply answering with insert all makes no sense. If you had replied with something like
"Check out the documentation for INSERT ALL" That would have been useful. Your post was not.
[Edit]
Addendum
In addition to it being ridiculously vague, it is an innappropriate solution in this instance.
[Updated on: Tue, 11 September 2007 06:42] Report message to a moderator
|
|
|
|
Re: insert statements [message #266964 is a reply to message #266630] |
Wed, 12 September 2007 02:19   |
gstijl
Messages: 3 Registered: September 2007
|
Junior Member |
|
|
the definition of the table :
CREATE TABLE TB_CRN002
(
PROFILEUID VARCHAR2(10 BYTE),
TARGETID VARCHAR2(50 BYTE),
USERNAME VARCHAR2(96 BYTE),
NOTES VARCHAR2(128 BYTE)
)
TABLESPACE TS_CRN001
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
The insert statements :
insert into tb_crn002 (profileuid,targetid,username) values ('***','****','****');
insert into tb_crn002 (profileuid,targetid,username) values ('****','****','****')
error ; invalid character
If I leave the ; then i get sql command not properly ended.
The insert all is not an option because I have one big textfile with all insert commands and not a table where i'm copying from.
Any help appreciated...
Gerry
|
|
|
Re: insert statements [message #266971 is a reply to message #266964] |
Wed, 12 September 2007 02:28   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
In SQL*Plus, everything seems to be fine with my 10g:SQL> CREATE TABLE TB_CRN002
2 (
3 PROFILEUID VARCHAR2(10 BYTE),
4 TARGETID VARCHAR2(50 BYTE),
5 USERNAME VARCHAR2(96 BYTE),
6 NOTES VARCHAR2(128 BYTE)
7 );
Table created.
SQL> edit p.sql
SQL> @p
1 row created.
1 row created.
SQL> $type p.sql
insert into tb_crn002 (profileuid,targetid,username) values ('***','****','****');
insert into tb_crn002 (profileuid,targetid,username) values ('****','****','****');
SQL>
However, two consecutive INSERT (or any other) statements (with no blank line between them) will raise an "invalid character" error when ran separately from TOAD using the "Execute statement" button.
Could you do the copy-paste as I did so that we could see how it looks like on your database?
|
|
|
Re: insert statements [message #266973 is a reply to message #266964] |
Wed, 12 September 2007 02:31  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
What is your client tool?
Regards
Michel
|
|
|
Goto Forum:
Current Time: Thu Feb 06 13:58:52 CST 2025
|