| newbee: simple sql loader script [message #121304] |
Fri, 27 May 2005 09:17  |
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
|
Member |
|
|
hello
i'm totally new to Oracle and SQL Loader. i'm used to write scripts for sql server, but not for oracle. if someone helps me further with this simple script so
i can work further on it.
Let's say:
I have a database with one table Zipcodes and its columns are:
- zipcode
- cityname
- language
And i have a csv file update.csv that contains the new values, for example this csv file contains three lines:
1000,brussels,N
2000,antwerp,N
8000,ostend,N
the ctl loader file that has to be created should update the cityname and the language column in the dateabase for the correct row, based on the zipcode like in the csv file provided.
an ms sql server script would be:
UPDATE Zipcodes
SET cityname = 'brussels', language = 'n'
WHERE zipcode = '1000'
but now, how do i write the ctl file for above csv file?
thanks in advance!!
[Updated on: Fri, 27 May 2005 09:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: newbee: simple sql loader script [message #121316 is a reply to message #121315] |
Fri, 27 May 2005 09:52  |
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
|
Member |
|
|
| r2d2dev wrote on Fri, 27 May 2005 10:51 |
| macdba wrote on Fri, 27 May 2005 10:25 |
then I will create one for u.
|
I understand the ctls on the link you provided, but how to perform the above update, i can not find. if you could create this simple ctl for me, it would be very nice, so i can work further on it.
|
|
|
|
|
| Re: newbee: simple sql loader script [message #121320 is a reply to message #121304] |
Fri, 27 May 2005 10:21 |
macdba
Messages: 27
Registered: May 2005
Location: US
|
Junior Member |
|
|
As Mr Mahesh as suggested, You can not update the records using SQL* Loader. You have to correct your input file before loading. Write a script that will parse a data file and update it as per ur condition. So data file will have correct records to be loaded.
OR follow these steps....
This is from the Oracle Documentation.
Updating Existing Rows
The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:
1. Load your data into a work table.
2. Use the SQL language UPDATE statement with correlated subqueries.
3. Drop the work table.
rgds
--Mak
|
|
|
|
| execute statements in a file [message #121462 is a reply to message #121304] |
Mon, 30 May 2005 00:28 |
r2d2dev
Messages: 75
Registered: May 2005
Location: bxl
|
Member |
|
|
hello,
In another topic i found that you can not use sqlloader to do row-per-row updates in DB.
So now i made a script that makes creates a file with all update-statements from my csv file.
But, since I'm new to Oracle, is there a way to be able to let the script execute from the command file?
(with sql loader it would be: sqlldr control=ctlfile.ctl)
What can i do now with my file (lets say myfile.sql) with over 100 update statementes to let them execute?
|
|
|
|
| Re: execute statements in a file [message #121470 is a reply to message #121462] |
Mon, 30 May 2005 01:33 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I believe it would be a lot simpler to use the first suggestion of loading the data into a staging table, then using SQL to update your table from the staging table. You have not said what version of Oracle you are using. If you are using at least version 9i, then you can use an external table instead of SQL*Loader if you like. Please see the demonstration below using SQL*Loader to load the data into a staging table, then update using SQL.
-- table that you already have and data for testing:
scott@ORA92> CREATE TABLE Zipcodes
2 (zipcode NUMBER,
3 cityname VARCHAR2(15),
4 language VARCHAR2(15))
5 /
Table created.
scott@ORA92> INSERT ALL
2 INTO zipcodes VALUES (1000, 'old_city1', NULL)
3 INTO zipcodes VALUES (2000, 'old_city2', NULL)
4 INTO zipcodes VALUES (8000, 'old_city3', NULL)
5 SELECT * FROM DUAL
6 /
3 rows created.
scott@ORA92> SELECT * FROM zipcodes
2 /
ZIPCODE CITYNAME LANGUAGE
---------- --------------- ---------------
1000 old_city1
2000 old_city2
8000 old_city3
-- staging table:
scott@ORA92> CREATE TABLE Zipcodes_new
2 (zipcode NUMBER,
3 cityname VARCHAR2(15),
4 language VARCHAR2(15))
5 /
Table created.
-- update.ctl SQL*Loader control file:
LOAD DATA
INFILE 'update.csv'
INTO TABLE zipcodes_new
FIELDS TERMINATED BY ','
(zipcode, cityname, language)
-- load data into zipcodes_new staging table from update.csv file:
scott@ORA92> HOST SQLLDR scott/tiger CONTROL=update.ctl LOG=update.log
-- update zipcodes table from data in zipcodes_new staging table:
scott@ORA92> UPDATE Zipcodes
2 SET (cityname, language) =
3 (SELECT cityname, language
4 FROM zipcodes_new
5 WHERE zipcodes_new.zipcode = zipcodes.zipcode)
6 WHERE EXISTS
7 (SELECT *
8 FROM zipcodes_new
9 WHERE zipcodes_new.zipcode = zipcodes.zipcode)
10 /
3 rows updated.
-- results:
scott@ORA92> SELECT * FROM zipcodes
2 /
ZIPCODE CITYNAME LANGUAGE
---------- --------------- ---------------
1000 brussels N
2000 antwerp N
8000 ostend N
scott@ORA92>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
