473,225 Members | 1,592 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

IMPORT with some defaults

Hi!

I have a ASCII file that I need to import with METHOD L, since columns are
fixed length. The problem is that I have more columns in a table that I'm
importing to that there are columns in the file. Is it possible to specify
some default values for columns that are not part of ASCII file? I'd like
that default value to be used in INSERT_UPDATE import option.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 6 '06 #1
5 3306
Kovi,

Have you looked at the column mapping in the IMPORT command?
Any column not listed should get the column DEFAULT.
Assuming you have the table for yourself you could just set the default
with ALTER TABLE (even make it a generated column if you wish).
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 6 '06 #2
Serge Rielau wrote:
Kovi,

Have you looked at the column mapping in the IMPORT command?
Any column not listed should get the column DEFAULT.
Assuming you have the table for yourself you could just set the default
with ALTER TABLE (even make it a generated column if you wish).
Cheers
Serge
Yes, I was also thinking something like that.
So...
If I have a table TABLE1 as:
CREATE TABLE TABLE1 (
COL1 BIGINT NOT NULL,
COL2 DECIMAL(18, 2),
COL3 VARCHAR(10),
COL4 BIGINT DEFAULT 9)

And IMPORT like:
IMPORT FROM FILE OF ASC MODIFIED BY DATEFORMAT="YYYYMMDD"
TIMEFORMAT="HHMMSS" IMPLIEDDECIMAL STRIPTBLANKS METHOD L (1 5, 6 10) NULL
INDICATORS (0, 0) MESSAGES FILE.MSG INSERT_UPDATE INTO TABLE1(COL1, COL2,
NULL, DEFAULT) ?

I'm not sure this will work.

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 6 '06 #3
Gregor Kovac wrote:
Hi!

I have a ASCII file that I need to import with METHOD L, since columns are
fixed length. The problem is that I have more columns in a table that I'm
importing to that there are columns in the file. Is it possible to specify
some default values for columns that are not part of ASCII file? I'd like
that default value to be used in INSERT_UPDATE import option.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
ANother option is to CREATE a separate import TABLE that matches the
file exactly. Import into that TABLE and then issue a standard INSERT
statement from that TABLE into the real TABLE. That should give you
more control over the process.

B.

Nov 6 '06 #4
Brian Tkatch wrote:
Gregor Kovac wrote:
>Hi!

I have a ASCII file that I need to import with METHOD L, since columns
are fixed length. The problem is that I have more columns in a table that
I'm importing to that there are columns in the file. Is it possible to
specify some default values for columns that are not part of ASCII file?
I'd like that default value to be used in INSERT_UPDATE import option.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

ANother option is to CREATE a separate import TABLE that matches the
file exactly. Import into that TABLE and then issue a standard INSERT
statement from that TABLE into the real TABLE. That should give you
more control over the process.

B.
Yes, but I don't really like this option. Why? Since there can be INSERTs
and UPDATEs and there are several fields in a primary key I don't want to
write complex SQL statements, if I don't need them (providing Serge gives
me a working IMPORT statement :))) ).

Best regards,
Kovi

--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Nov 6 '06 #5
Brian Tkatch wrote:
ANother option is to CREATE a separate import TABLE that matches the
file exactly. Import into that TABLE and then issue a standard INSERT
statement from that TABLE into the real TABLE. That should give you
more control over the process.
Another alternative (avoiding the additional base table) would be to define
a view comprised only of the columns in question. Add some INSTEAD OF
triggers to the view for INSERT/UPDATE statements to provide the values for
missing columns, and then finally IMPORT into the view.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 6 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

303
by: mike420 | last post by:
In the context of LATEX, some Pythonista asked what the big successes of Lisp were. I think there were at least three *big* successes. a. orbitz.com web site uses Lisp for algorithms, etc. b....
5
by: steve | last post by:
Hi, When I copy tables in a database from one server to another using enterprise manager, everything copies ok, except for field defaults. Has anyone seen this, and what is the solution? --...
2
by: Scott | last post by:
I am using DTS to import tables into my new database from another database, both of which reside on the same server. All the tables, fields and data copy over fine; however when I go into design...
10
by: serge | last post by:
I am doing a little research on Google about this topic and I ran into this thread: ...
8
by: btober | last post by:
I'm finding that column defaults are not being assigned to nulls when I do an insert by way of a an ON INSERT rule on a view. For example, the following script \set ON_ERROR_STOP ON \c...
2
by: stefan | last post by:
Hi, for a school project I'm trying to analyze some python modules. I just struggeld over a little detail I cannot solve: shortened example of walking trough an AST with my own visitor: ...
4
by: Zytan | last post by:
I have VB code that shows how to import a DLL function. I compiled it. I used .NET Reflector to see the C# code, and it shows this: private static extern bool MethodName( ref string...
0
by: 123bargains | last post by:
Hello, I have a question on importing a database from MSSQL 2000 to MSSQL 2005. I hope someone on here can help me answer it. I am trying to import a database from MSSQL 2000 to 2005. But, when I...
5
by: George Sakkis | last post by:
I maintain a few configuration files in Python syntax (mainly nested dicts of ints and strings) and use execfile() to read them back to Python. This has been working great; it combines the...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.