Database Tutorial

Course Tutorial Site

Site Admin

Import Transaction Units

with 13 comments

This page explains basic transaction management, and demonstrates how to move the data from the denormalized file to a normalized set of files. It’s important to follow the instructions carefully. You can find a basic transaction article here.

It is important that you note external files are frequently in zero normal form, which is also known as unnormalized form (UNF). That means some of the fields in the .csv file repeat while others don’t. Part of any import process requires that you transform the UNF data into your normalized data model. Import processes are typically programmatic interfaces and sometimes APIs. You must ensure in those APIs that any transformation achieves at least 1NF because that avoids most insertion anomalies. You can refer back to this page for more on normalization.

Transaction Model
OracleMySQLSQL Server

Oracle

This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement.

Step #1 : Create a virtual directory

You can create a virtual directory without a physical directory but it won’t work when you try to access it. Therefore, you should create the physical directory first. Assuming you’ve created a /u01/app/oracle/upload file directory on the Windows platform, you can then create a virtual directory and grant permissions to the student user as the SYS privileged user.

The syntax for these steps is:

CREATE DIRECTORY upload AS '/u01/app/oracle/upload';
GRANT READ, WRITE ON DIRECTORY upload TO student;

Step #2 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_import.csv in the /u01/app/oracle/upload directory or folder. If you attempt to do this in Windows, you need to disable Windows UAC before performing this step.

Place the following in the kingdom_import.csv file. The trailing commas aren’t too meaningful in Oracle but they’re very helpful if you use the file in MySQL. A key element in creating this files requires that you avoid trailing line returns at the bottom of the file because they’re inserted as null values. There should be no lines after the last row of data.

'Narnia',77600,'Peter the Magnificent','20-MAR-1272','19-JUN-1292',
'Narnia',77600,'Edmund the Just','20-MAR-1272','19-JUN-1292',
'Narnia',77600,'Susan the Gentle','20-MAR-1272','19-JUN-1292',
'Narnia',77600,'Lucy the Valiant','20-MAR-1272','19-JUN-1292',
'Narnia',42100,'Peter the Magnificent','12-APR-1531','31-MAY-1531',
'Narnia',42100,'Edmund the Just','12-APR-1531','31-MAY-1531',
'Narnia',42100,'Susan the Gentle','12-APR-1531','31-MAY-1531',
'Narnia',42100,'Lucy the Valiant','12-APR-1531','31-MAY-1531',
'Camelot',15200,'King Arthur','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Lionel','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Bors','10-MAR-0631','12-DEC-0635',
'Camelot',15200,'Sir Bors','10-MAR-0640','12-DEC-0686',
'Camelot',15200,'Sir Galahad','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Gawain','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Tristram','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Percival','10-MAR-0631','12-DEC-0686',
'Camelot',15200,'Sir Lancelot','30-SEP-0670','12-DEC-0682',

Step #3 : Reconnect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

CONNECT student@xe

Step #4 : Run the script that creates tables and sequences

Copy the following into a create_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

-- Conditionally drop tables and sequences.
BEGIN
  FOR i IN (SELECT TABLE_NAME
            FROM   user_tables
            WHERE  TABLE_NAME IN ('KINGDOM','KNIGHT','KINGDOM_KNIGHT_IMPORT')) LOOP 
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
  FOR i IN (SELECT sequence_name
            FROM   user_sequences
            WHERE  sequence_name IN ('KINGDOM_S1','KNIGHT_S1')) LOOP 
    EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.sequence_name;
  END LOOP;
END;
/
 
-- Create normalized kingdom table.
CREATE TABLE kingdom
( kingdom_id    NUMBER
, kingdom_name  VARCHAR2(20)
, population    NUMBER);
 
-- Create a sequence for the kingdom table.
CREATE SEQUENCE kingdom_s1;
 
-- Create normalized knight table.
CREATE TABLE knight
( knight_id             NUMBER
, knight_name           VARCHAR2(24)
, kingdom_allegiance_id NUMBER
, allegiance_start_date DATE
, allegiance_end_date   DATE);
 
-- Create a sequence for the knight table.
CREATE SEQUENCE knight_s1;
 
-- Create external import table.
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR2(20)
, population            NUMBER
, knight_name           VARCHAR2(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE)
  ORGANIZATION EXTERNAL
  ( TYPE oracle_loader
    DEFAULT DIRECTORY upload
    ACCESS PARAMETERS
    ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
      BAFFLE      'UPLOAD':'kingdom_import.bad'
      DISCARDFILE 'UPLOAD':'kingdom_import.dis'
      LOGFILE     'UPLOAD':'kingdom_import.log'
      FIELDS TERMINATED BY ','
      OPTIONALLY ENCLOSED BY "'"
      MISSING FIELD VALUES ARE NULL )
    LOCATION ('kingdom_import.csv'))
REJECT LIMIT UNLIMITED;

Step #5 : Test your access to the external table

There a number of things that could go wrong with setting up an external table, such as file permissions. Before moving on to the balance of the steps, you should test what you’ve done. Run the following query from the student account to check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6
7
8
9

COL kingdom_name FORMAT A8 HEADING "Kingdom|Name"
COL population   FORMAT 99999999 HEADING "Population"
COL knight_name  FORMAT A30 HEADING "Knight Name"
SELECT   kingdom_name
,        population
,        knight_name
,        TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date
,        TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date
FROM     kingdom_knight_import;

Step #6 : Create the upload procedure

Copy the following into a create_upload_procedure.sql file within a directory of your choice. Then, run it as the student account.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- Create a procedure to wrap the transaction.
CREATE OR REPLACE PROCEDURE upload_kingdom IS 
BEGIN
  -- Set save point for an all or nothing transaction.
  SAVEPOINT starting_point;
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO kingdom target
  USING (SELECT   DISTINCT
                  k.kingdom_id
         ,        kki.kingdom_name
         ,        kki.population
         FROM     kingdom_knight_import kki LEFT JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population) SOURCE
  ON (target.kingdom_id = SOURCE.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET kingdom_name = SOURCE.kingdom_name
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( kingdom_s1.nextval
  , SOURCE.kingdom_name
  , SOURCE.population);
 
  -- Insert or update the table, which makes this rerunnable when the file hasn't been updated.  
  MERGE INTO knight target
  USING (SELECT   kn.knight_id
         ,        kki.knight_name
         ,        k.kingdom_id
         ,        kki.allegiance_start_date AS start_date
         ,        kki.allegiance_end_date AS end_date
         FROM     kingdom_knight_import kki INNER JOIN kingdom k
         ON       kki.kingdom_name = k.kingdom_name
         AND      kki.population = k.population LEFT JOIN knight kn 
         ON       k.kingdom_id = kn.kingdom_allegiance_id
         AND      kki.knight_name = kn.knight_name
         AND      kki.allegiance_start_date = kn.allegiance_start_date
         AND      kki.allegiance_end_date = kn.allegiance_end_date) SOURCE
  ON (target.kingdom_allegiance_id = SOURCE.kingdom_id)
  WHEN MATCHED THEN
  UPDATE SET allegiance_start_date = SOURCE.start_date
  ,          allegiance_end_date = SOURCE.end_date
  WHEN NOT MATCHED THEN
  INSERT VALUES
  ( knight_s1.nextval
  , SOURCE.knight_name
  , SOURCE.kingdom_id
  , SOURCE.start_date
  , SOURCE.end_date);
 
  -- Save the changes.
  COMMIT;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO starting_point;
    RETURN;
END;
/

Step #7 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

EXECUTE upload_kingdom;

Step #8 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

-- Check the kingdom table.
SELECT * FROM kingdom;
 
-- Format Oracle output.
COLUMN knight_id             FORMAT 999 HEADING "Knight|ID #"
COLUMN knight_name           FORMAT A23 HEADING "Knight Name"
COLUMN kingdom_allegiance_id FORMAT 999 HEADING "Kingdom|Allegiance|ID #"
COLUMN allegiance_start_date FORMAT A11 HEADING "Allegiance|Start Date"
COLUMN allegiance_end_date   FORMAT A11 HEADING "Allegiance|End Date"
SET PAGESIZE 999
 
-- Check the knight table.
SELECT   knight_id
,        knight_name
,        kingdom_allegiance_id
,        TO_CHAR(allegiance_start_date,'DD-MON-YYYY') AS allegiance_start_date
,        TO_CHAR(allegiance_end_date,'DD-MON-YYYY') AS allegiance_end_date
FROM     knight;

It should display the following information:

KINGDOM_ID KINGDOM_NAME         POPULATION
---------- -------------------- ----------
         1 Narnia                    42100
         2 Narnia                    77600
         3 Camelot                   15200
 
                                  Kingdom
Knight                         Allegiance Allegiance  Allegiance
  ID # Knight Name                   ID # Start Date  End Date
------ ----------------------- ---------- ----------- -----------
     1 Peter the Magnificent            2 20-MAR-1272 19-JUN-1292
     2 Edmund the Just                  2 20-MAR-1272 19-JUN-1292
     3 Susan the Gentle                 2 20-MAR-1272 19-JUN-1292
     4 Lucy the Valiant                 2 20-MAR-1272 19-JUN-1292
     5 Peter the Magnificent            1 12-APR-1531 31-MAY-1531
     6 Edmund the Just                  1 12-APR-1531 31-MAY-1531
     7 Susan the Gentle                 1 12-APR-1531 31-MAY-1531
     8 Lucy the Valiant                 1 12-APR-1531 31-MAY-1531
     9 King Arthur                      3 10-MAR-0631 12-DEC-0686
    10 Sir Lionel                       3 10-MAR-0631 12-DEC-0686
    11 Sir Bors                         3 10-MAR-0631 12-DEC-0635
    12 Sir Bors                         3 10-MAR-0640 12-DEC-0686
    13 Sir Galahad                      3 10-MAR-0631 12-DEC-0686
    14 Sir Gawain                       3 10-MAR-0631 12-DEC-0686
    15 Sir Tristram                     3 10-MAR-0631 12-DEC-0686
    16 Sir Percival                     3 10-MAR-0631 12-DEC-0686
    17 Sir Lancelot                     3 30-SEP-0670 12-DEC-0682

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

MySQL

This is an example of how you would upload data from a flat file, or Comma Separated Value (CSV) file. It’s important to note that in the file upload you are transferring information that doesn’t have surrogate key values by leveraing joins inside a MERGE statement.

Step #1 : Position your CSV file in the physical directory

After creating the virtual directory, copy the following contents into a file named kingdom_mysql_import.csv in the /u01/app/mysql/upload directory or folder. If you do this on Windows, you must disable Windows UAC before performing this step.

Place the following in the kingdom_mysql_import.csv file. The trailing commas are meaningful in MySQL and avoid problems when reading CSV files.

Narnia, 77600,'Peter the Magnificent',12720320,12920609,
Narnia, 77600,'Edmund the Just',12720320,12920609,
Narnia, 77600,'Susan the Gentle',12720320,12920609,
Narnia, 77600,'Lucy the Valiant',12720320,12920609,
Narnia, 42100,'Peter the Magnificent',15310412,15310531,
Narnia, 42100,'Edmund the Just',15310412,15310531,
Narnia, 42100,'Susan the Gentle',15310412,15310531,
Narnia, 42100,'Lucy the Valiant',15310412,15310531,
Camelot, 15200,'King Arthur',06310310,06861212,
Camelot, 15200,'Sir Lionel',06310310,06861212,
Camelot, 15200,'Sir Bors',06310310,06351212,
Camelot, 15200,'Sir Bors',06400310,06861212,
Camelot, 15200,'Sir Galahad',06310310,06861212,
Camelot, 15200,'Sir Gawain',06310310,06861212,
Camelot, 15200,'Sir Tristram',06310310,06861212,
Camelot, 15200,'Sir Percival',06310310,06861212,
Camelot, 15200,'Sir Lancelot',06700930,06821212,

Step #2 : Connect as the student user

Disconnect and connect as the student user, or reconnect as the student user. The reconnect syntax that protects your password is:

mysql -ustudent -p

Connect to the studentdb database, like so:

mysql> USE sampledb;

Step #3 : Run the script that creates tables and sequences

Copy the following into a create_mysql_kingdom_upload.sql file within a directory of your choice. Then, run it as the student account.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- This enables dropping tables with foreign key dependencies.
-- It is specific to the InnoDB Engine.
SET FOREIGN_KEY_CHECKS = 0; 
 
-- Conditionally drop objects.
SELECT 'KINGDOM' AS "Drop Table";
DROP TABLE IF EXISTS kingdom;
 
SELECT 'KNIGHT' AS "Drop Table";
DROP TABLE IF EXISTS knight;
 
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Drop Table";
DROP TABLE IF EXISTS kingdom_knight_import;
 
-- Create normalized kingdom table.
SELECT 'KINGDOM' AS "Create Table";
CREATE TABLE kingdom
( kingdom_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, kingdom_name  VARCHAR(20)
, population    INT UNSIGNED) ENGINE=INNODB;
 
-- Create normalized knight table.
SELECT 'KNIGHT' AS "Create Table";
CREATE TABLE knight
( knight_id             INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, knight_name           VARCHAR(24)
, kingdom_allegiance_id INT UNSIGNED
, allegiance_start_date DATE
, allegiance_end_date   DATE
, CONSTRAINT fk_kingdom FOREIGN KEY (kingdom_allegiance_id)
  REFERENCES kingdom (kingdom_id)) ENGINE=INNODB;
 
-- Create external import table in memory only - disappears after rebooting the mysqld service.
SELECT 'KINGDOM_KNIGHT_IMPORT' AS "Create Table";
CREATE TABLE kingdom_knight_import
( kingdom_name          VARCHAR(20)
, population            INT UNSIGNED
, knight_name           VARCHAR(24)
, allegiance_start_date DATE
, allegiance_end_date   DATE) ENGINE=MEMORY;

Step #4 : Load the data into your target upload table

There a number of things that could go wrong but generally when you choose LOCAL there aren’t any problems. Run the following query from the student account while using the studentdb database, and check whether or not you can access the kingdom_import.csv file.

1
2
3
4
5
6

LOAD DATA LOCAL INFILE '/u01/app/mysql/upload/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

Step #5 : Create the upload procedure

Copy the following into a create_mysql_upload_procedure.sql file within a directory of your choice. You should note that unlike Oracle’s MERGE statement, this is done with the ON DUPLICATE KEY clause and requires actual values not a source query. This presents few options other than a stored routine, known as a stored procedure. As you can see from the code, there’s a great deal of complexity to the syntax and a much more verbose implementation than Oracle’s equivalent PL/SQL.

Then, run it as the student account.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150

-- Conditionally drop the procedure.
SELECT 'UPLOAD_KINGDOM' AS "Drop Procedure";
DROP PROCEDURE IF EXISTS upload_kingdom;
 
-- Reset the execution delimiter to create a stored program.
DELIMITER $$
 
-- The parentheses after the procedure name must be there or the MODIFIES SQL DATA raises an compile time exception.
CREATE PROCEDURE upload_kingdom() MODIFIES SQL DATA
 
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_kingdom_id            INT UNSIGNED;
  DECLARE lv_kingdom_name          VARCHAR(20);
  DECLARE lv_population            INT UNSIGNED;
  DECLARE lv_knight_id             INT UNSIGNED;
  DECLARE lv_knight_name           VARCHAR(24);
  DECLARE lv_kingdom_allegiance_id INT UNSIGNED;
  DECLARE lv_allegiance_start_date DATE;
  DECLARE lv_allegiance_end_date   DATE;
 
  /* Declare a handler variables. */
  DECLARE duplicate_key INT DEFAULT 0;
  DECLARE foreign_key   INT DEFAULT 0;
  DECLARE fetched       INT DEFAULT 0;
 
  /* Cursors must come after variables and before event handlers. */
 
  /* Declare a SQL cursor with a left join on the natural key. */  
  DECLARE kingdom_cursor CURSOR FOR
    SELECT   DISTINCT
             k.kingdom_id
    ,        kki.kingdom_name
    ,        kki.population
    FROM     kingdom_knight_import kki LEFT JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population; 
 
  /* Declare a SQL cursor with a join on the natural key. */  
  DECLARE knight_cursor CURSOR FOR
    SELECT   kn.knight_id
    ,        kki.knight_name
    ,        k.kingdom_id
    ,        kki.allegiance_start_date AS start_date
    ,        kki.allegiance_end_date AS end_date
    FROM     kingdom_knight_import kki INNER JOIN kingdom k
    ON       kki.kingdom_name = k.kingdom_name
    AND      kki.population = k.population LEFT JOIN knight kn 
    ON       k.kingdom_id = kn.kingdom_allegiance_id
    AND      kki.knight_name = kn.knight_name
    AND      kki.allegiance_start_date = kn.allegiance_start_date
    AND      kki.allegiance_end_date = kn.allegiance_end_date; 
 
  /* Event handlers must always be last in the declaration section. */
 
  /* Declare a duplicate key handler */
  DECLARE CONTINUE HANDLER FOR 1062 SET duplicate_key = 1;
  DECLARE CONTINUE HANDLER FOR 1216 SET foreign_key = 1;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* ---------------------------------------------------------------------- */
 
  /* Start transaction context. */
  START TRANSACTION;
 
  /* Set savepoint. */  
  SAVEPOINT both_or_none;
 
  /* Open a local cursor. */  
  OPEN kingdom_cursor;
  cursor_kingdom: LOOP
 
    FETCH kingdom_cursor
    INTO  lv_kingdom_id
    ,     lv_kingdom_name
    ,     lv_population;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_kingdom; END IF;
 
    INSERT INTO kingdom
    VALUES
    ( lv_kingdom_id
    , lv_kingdom_name
    , lv_population ) 
    ON DUPLICATE KEY
    UPDATE kingdom_name = lv_kingdom_name;
 
  END LOOP cursor_kingdom;
  CLOSE kingdom_cursor;
 
  /* Reset the continue handler to zero. */
  SET fetched = 0;  
 
  /* Open a local cursor. */  
  OPEN knight_cursor;
  cursor_knight: LOOP
 
    /* Fetch records until they're all read, and a NOT FOUND SET is returned. */  
    FETCH knight_cursor
    INTO  lv_knight_id
    ,     lv_knight_name
    ,     lv_kingdom_allegiance_id
    ,     lv_allegiance_start_date
    ,     lv_allegiance_end_date;
 
    /* Place the catch handler for no more rows found
       immediately after the fetch operation.          */
    IF fetched = 1 THEN LEAVE cursor_knight; END IF;
 
    INSERT INTO knight
    VALUES
    ( lv_knight_id
    , lv_knight_name
    , lv_kingdom_allegiance_id
    , lv_allegiance_start_date
    , lv_allegiance_end_date ) 
    ON DUPLICATE KEY
    UPDATE knight_name = lv_knight_name;
 
  END LOOP cursor_knight;
  CLOSE knight_cursor;
 
  /* Reset the continue handler to zero. */
  SET fetched = 0;  
 
  /* ---------------------------------------------------------------------- */
 
  /* This acts as an exception handling block. */  
  IF duplicate_key = 1 OR foreign_key = 1 THEN
 
    /* This undoes all DML statements to this point in the procedure. */
    ROLLBACK TO SAVEPOINT both_or_none;
 
  ELSE
 
    /* This commits the writes. */
    COMMIT;
 
  END IF;
 
END;
$$
 
-- Reset the delimiter to the default.
DELIMITER ;

Step #6 : Run the upload procedure

You can run the file by calling the stored procedure built by the script. The procedure ensures that records are inserted or updated into their respective tables.

CALL upload_kingdom;

Step #7 : Test the results of the upload procedure

You can test whether or not it worked by running the following queries.

-- Check the kingdom table.
SELECT * FROM kingdom;
SELECT * FROM knight;

It should display the following information:

+------------+--------------+------------+
| kingdom_id | kingdom_name | population |
+------------+--------------+------------+
|          1 | Narnia       |      77600 |
|          2 | Narnia       |      42100 |
|          3 | Camelot      |      15200 |
+------------+--------------+------------+
 
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
| knight_id | knight_name             | kingdom_allegiance_id | allegiance_start_date | allegiance_end_date |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+
|         1 | 'Peter the Magnificent' |                     1 | 1272-03-20            | 1292-06-09          |
|         2 | 'Edmund the Just'       |                     1 | 1272-03-20            | 1292-06-09          |
|         3 | 'Susan the Gentle'      |                     1 | 1272-03-20            | 1292-06-09          |
|         4 | 'Lucy the Valiant'      |                     1 | 1272-03-20            | 1292-06-09          |
|         5 | 'Peter the Magnificent' |                     2 | 1531-04-12            | 1531-05-31          |
|         6 | 'Edmund the Just'       |                     2 | 1531-04-12            | 1531-05-31          |
|         7 | 'Susan the Gentle'      |                     2 | 1531-04-12            | 1531-05-31          |
|         8 | 'Lucy the Valiant'      |                     2 | 1531-04-12            | 1531-05-31          |
|         9 | 'King Arthur'           |                     3 | 0631-03-10            | 0686-12-12          |
|        10 | 'Sir Lionel'            |                     3 | 0631-03-10            | 0686-12-12          |
|        11 | 'Sir Bors'              |                     3 | 0631-03-10            | 0635-12-12          |
|        12 | 'Sir Bors'              |                     3 | 0640-03-10            | 0686-12-12          |
|        13 | 'Sir Galahad'           |                     3 | 0631-03-10            | 0686-12-12          |
|        14 | 'Sir Gawain'            |                     3 | 0631-03-10            | 0686-12-12          |
|        15 | 'Sir Tristram'          |                     3 | 0631-03-10            | 0686-12-12          |
|        16 | 'Sir Percival'          |                     3 | 0631-03-10            | 0686-12-12          |
|        17 | 'Sir Lancelot'          |                     3 | 0670-09-30            | 0682-12-12          |
+-----------+-------------------------+-----------------------+-----------------------+---------------------+

You can rerun the procedure to check that it doesn’t alter any information, then you could add a new knight to test the insertion portion.

SQL Server

Written by michaelmclaughlin

October 8th, 2009 at 9:21 pm

Posted in

13 Responses to 'Import Transaction Units'

Subscribe to comments with RSS or TrackBack to 'Import Transaction Units'.

  1. Step 2 of the CSV external table creation example page describes the creation of the virtual directory C:\Download. The steps in this example page, however, specify the creation of the virtual directory C:\Data\Download. Does that mean we have to create 2 directories, or are the two examples supposed to be the same?

    Dane Christiansen

    4 Mar 10 at 2:25 pm

  2. I copied all the scripts from your page, but when I select * from kingdom, I have a null value inserted preceding the narnia kingdom.

    Dane Christiansen

    4 Mar 10 at 2:45 pm

  3. Dane, you can’t have any trailing empty lines. There should be no line return at the end of the last line. Try removing that, and the null value will go away.

    michaelmclaughlin

    4 Mar 10 at 11:38 pm

  4. The procedure will not add additional knights unless I run the scripts that remove the tables and recreate them.

    Rudy

    9 Jun 10 at 4:05 pm

  5. in the oracle section you say:
    “Assuming you’ve created a C:\Data\Download file directory on the Windows platform, …”

    and then when you create the virtual directory you use this command:
    “CREATE DIRECTORY download AS ‘C:\Download’;”

    the virtual directory does not point to the real directory. could you fix this?

    Daniel

    16 Jun 10 at 1:04 pm

  6. Thanks Daniel! It’s fixed.

    michaelmclaughlin

    21 Jun 10 at 9:41 pm

  7. In Oracle, you need to only update the *.csv file contents and rerun the process. MySQL is different. After you update the *.csv file in MySQL all you need to re-run is this:

    1
2
3
4
5
6

    LOAD DATA LOCAL INFILE 'c:/Data/kingdom_mysql_import.csv'
INTO TABLE kingdom_knight_import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY 'rn';

    michaelmclaughlin

    21 Jun 10 at 9:46 pm

  8. MySQL Linux users will encounter a bug with the LOAD DATA statement. Using the code as is, the code will only read the top row from the CSV file. The LINES TERMINATED BY needs to be altered to ‘\n’ or simply omitted (since it’s the default value as MySQL was built to run on Linux). This will find the correct line terminations and load all data from the CSV to the internal table.

    Tighe Racicot

    13 Mar 13 at 9:06 pm

  9. Tighe, Yes, that’s covered in lecture and in the book but it will be updated next term when I edit the pages. Thanks.

    michaelmclaughlin

    18 Mar 14 at 11:24 am

  10. In Step# 3 that creates tables and sequences, the drop statements will not work unless the tables are referenced in the same case as the case that is used during their creation.

    So the following will work:

    DROP TABLE IF EXISTS kingdom;
CREATE TABLE kingdom....;

    whereas the following will NOT work:

    DROP TABLE IF EXISTS KINGDOM;
CREATE TABLE kingdom....;

    because of the difference in cases…

    Gabe Ventilla

    10 Nov 14 at 7:16 pm

  11. Gabe, good catch. They’re fixed and now lowercase.

    michaelmclaughlin

    11 Nov 14 at 12:20 am

  12. In Oracle Step #4 during the creation of the external import table kingdom_knight_import, all the references to the DOWNLOAD folder should be changed to the UPLOAD folder.

    Gabe Ventilla

    12 Nov 14 at 5:29 pm

  13. I’ve updated all references from Windows to Linux. The directories now point to /u01/app/oracle/upload or /u01/app/mysql/upload directories.

    michaelmclaughlin

    12 Nov 14 at 9:50 pm

Leave a Reply