All Forums Tools
16 Oct 2014
BTEQ IMPORT EXPORT DATA

Hello All,
When i ran the below code, all the 3 rows got exported to a text file. From there on, im importing it in the same script. When i do so the 1st record of the exported data is not getting loaded in the Target Table.
.run file=./logon.txt
.SET WIDTH 25;
.SET PAGELENGTH 50;
.SET SEPERATOR 0;
.SET PACK 2;
.SET SUPPRESS ON; 

.SET ERRORLEVEL(3807) SEVERITY 0; 
DROP TABLE DB.SourceT1;
DROP TABLE DB.TargetT1;
.SET ERRORLEVEL(3807) SEVERITY 8;
CREATE MULTISET TABLE DB.SourceT1
(
EmpId INT,
EmpName CHAR(10),
Salary DEC(5,2),
DeptId SMALLINT,
DeptName VARCHAR(10),
DOJ TIMESTAMP(6)
);
CREATE MULTISET TABLE DB.TargetT1
(
EmpId INT,
EmpName CHAR(10),
Salary DEC(5,2),
DeptId SMALLINT,
DeptName VARCHAR(10),
DOJ TIMESTAMP(6)
);
.IF ERRORCODE=0 THEN .GOTO INS1;
.GOTO END1;
.IF ACTIVITYCOUNT > 0 THEN .GOTO CONT;
.GOTO END1; 
.LABEL INS1
INS INTO DB.SourceT1(1,'Jugal',100.00,101,'SE','2014-10-15 01:00:00.000000');
INS INTO DB.SourceT1(2,'Hafiez',100.00,102,'MT','2014-10-10 01:00:00.000000');
INS INTO DB.SourceT1(3,'Raju',101.00,102,'BIZ','2014-10-10 01:00:00.000000');
.LABEL CONT
.EXPORT DATA FILE=./Export.txt 
SEL * FROM DB.SourceT1;
.EXPORT RESET;
.LABEL END1
.IMPORT DATA FILE=./Export.txt;
.QUIET ON;
USING 
EmpId (INT),
EmpName (CHAR(10)),
Salary (DEC(5,2)),
DeptId (SMALLINT),
DeptName (VARCHAR(10)),
DOJ (CHAR(26))
INS INTO DB.TargetT1(:EmpId,:EmpName,:Salary,:DeptId,: DeptName,:DOJ);
.LOGOFF;
 
Plz help me understand. Why the data is not getting copied.

dnoeth 4628 posts Joined 11/04
16 Oct 2014

Both DOJ (CHAR(26)) or DOJ (TIMESTAMP(6)) should work if the timestamp is exported in the correct format: YYYY-MM-DD HH:MI:SS.SSSSSS
So check the timestamp in your data file.

Dieter

16 Oct 2014

Hi Dieter,
I see all the 3 rows been exported and the TIMESTAMP being exported in a correct format.

Thanks
Jugal.

dnoeth 4628 posts Joined 11/04
18 Oct 2014

Hi Jugal,
then this might be related to some setting, don't know, maybe try SET SESSION DATEFORM = ANSIDATE

Dieter

You must sign in to leave a comment.