x

Can we skip certain columns while loading data ?

Can anybody explain to me how to skip certain columns of rows/records while loading data ? Thank you in advance.

more ▼

asked Jan 21, 2010 at 12:08 PM in Default

OracleApprender gravatar image

OracleApprender
771 71 73 75

How are you loading data? Are those columns NULLable? If so, just omit that column in your load, whether that be INSERT or SQL*Loader.
Jan 21, 2010 at 12:17 PM HillbillyToad
We are loading the data from a file to a database. Columns may have data or NULL.
Jan 22, 2010 at 12:28 PM OracleApprender
What is your load mechanism though? Running insert statements, using SQL*Loader, IMPORT, DataPump, 3rd party tool like Toad, what?
Jan 24, 2010 at 01:39 PM HillbillyToad
Direct Load mechanism. Running Insert statement is conventional mechanism.
Jan 25, 2010 at 09:09 AM OracleApprender
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want.

Look at this example: -- One cannot use POSTION(x:y) as it is stream data, there are no positional fields-the next field begins after some delimiter, not in column X. -->

LOAD DATA TRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ',' ( field1, field2 FILLER, field3 )

more ▼

answered Jan 25, 2010 at 09:15 AM

OracleApprender gravatar image

OracleApprender
771 71 73 75

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x375
x242
x50

asked: Jan 21, 2010 at 12:08 PM

Seen: 1396 times

Last Updated: Jan 22, 2010 at 06:29 AM