Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables 1. Customer_details and 2. Order_details.

1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name,

2.Order_details table --> fields-- Ord_Id(Primary Key), Cust_id

(There is no field for Customer name in Order_details table). I am trying to import customer orders to order_details from a CSV file through an external application. The CSV File contains the customer name not the Cust_ID. When inserting into order_details I need to look up the Cust_ID based on the name in the file and insert the Cust_ID into order_details instead. This needs to be implemented at the Database level not in the application code. I have been searching a lot for answers but cant figure it out. Could I possibly use a trigger to accomplish this?

Thanks, Acube.

share|improve this question
    
Why would you want to do that? Can the customer name change with each order? If you haven’t got a customer name field in order_details how can you insert it into this table? –  Jens W. Mar 13 at 12:11
    
I need to insert the Cust_id of customer name from customer_ details table into order_details table, in order to find customers name having orders. –  Acube Mar 13 at 12:18

3 Answers 3

Assuming that Customer_details table is populated you can do lookups on the fly with LOAD DATA INFILE by leveraging session variables and a SET clause

LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id = 
(
  SELECT cust_id
    FROM customer_details
   WHERE customer_name = @customer_name -- use a session variable to get an id
   LIMIT 1 -- make sure that exactly one row is returned
) 

Suppose we have a CSV file with the following content

ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"

Let's try import it:

mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
    -> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/order_details.txt'
    -> INTO TABLE order_details
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (ord_id, @customer_name) -- use a session variable to store a value read from the file
    -> SET cust_id =
    -> (
    ->   SELECT cust_id
    ->     FROM customer_details
    ->    WHERE customer_name = @customer_name -- use a session variable to get an id
    ->    LIMIT 1 -- this is to ensure that exactly one is returned
    -> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
|      1 |       1 |       1 | Customer1     |
|      2 |       2 |       2 | Customer2     |
|      3 |       2 |       2 | Customer2     |
|      4 |       4 |       4 | Customer4     |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)

There you have it.

share|improve this answer
    
@Acube Did it help? –  peterm Mar 15 at 22:21

First load the csv file in a temporary table with all columns like the csv input. Afterwards you can do an insert into order_details with a join to get the cust_id:

insert into order_details(ord_id, cust_id) select t.ord_id, c.cust_id from temporarytable t JOIN customer_details c on t.customername=c.customername

Maybe you have to insert new customers first into customer_details.

share|improve this answer

The way I normally accomplish this is to have the application load the data into a staging table and not directly into the orders table. Then you have the application call a stored procedure that holds all the logic mapping customer names to customer ID's and inserts that corrected data to the order_details table.

By using a staging table you can also massage the incoming data very easily in that same stored procedure IE: if for some reason the incoming file lists prices in a weird format you could correct that to match your current database formats.

Also by using a staging table if you have multiple people using this program you can set a job on the database server to run the SP at some interval (and not have the app call it on demand). Then people could continually load data into the staging table and if the data is not correct, you could kick it out and notify the person who added the data (if you have that tracking as part of your table).

share|improve this answer
    
how to do this for my problem statement –  Acube Mar 13 at 12:57
    
That depends on your database schema and the format of the file. With out those answering with code is just a random guess. –  Wind Raven Mar 13 at 12:58
    
What all information you need, i can provide for answering with code –  Acube Mar 13 at 13:26
    
I would need a copy of the file you are trying to import and the schema of all your tables. That is information that I personally would not want to be posted on a public forum. –  Wind Raven Mar 13 at 18:18
    
ok give your email id i will send you –  Acube Mar 13 at 19:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.