Andromedae93 Andromedae93 - 4 months ago 12
SQL Question

Add rows into a mysql table from a .sql file

I have a question on How I can insert a .sql file into a MySQL table which already contains lot of data ?

My .sql file looks like (1200 rows) :

--
-- Descriptif plan comptable SYSCOHADA (utf-8)
--

INSERT INTO llx_accounting_system (rowid, pcg_version, fk_pays, label, active) VALUES (10,'SYSCOHADA', 49, 'Plan comptable Ouest-Africain', 1);

INSERT INTO llx_accounting_account (rowid, fk_pcg_version, pcg_type, pcg_subtype, account_number, account_parent, label, active) VALUES (15000,'SYSCOHADA','CAPITAUX','XXXXXX','1',0,"Capital",'1');
INSERT INTO llx_accounting_account (rowid, fk_pcg_version, pcg_type, pcg_subtype, account_number, account_parent, label, active) VALUES (15001,'SYSCOHADA','CAPITAUX','XXXXXX','101',15000,"Capital social",'1');
INSERT INTO llx_accounting_account (rowid, fk_pcg_version, pcg_type, pcg_subtype, account_number, account_parent, label, active) VALUES (15002,'SYSCOHADA','CAPITAUX','XXXXXX','1011',15001,"Capital souscrit, non appele);


My MySQL table looks like :

mysql> describe llx_accounting_account ;
+----------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+-------------------+-----------------------------+
| rowid | int(11) | NO | PRI | NULL | auto_increment |
| entity | int(11) | NO | | 1 | |
| datec | datetime | YES | | NULL | |
| tms | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| fk_pcg_version | varchar(32) | NO | MUL | NULL | |
| pcg_type | varchar(20) | NO | | NULL | |
| pcg_subtype | varchar(20) | NO | | NULL | |
| account_number | varchar(32) | NO | MUL | NULL | |
| account_parent | varchar(32) | YES | | NULL | |
| label | varchar(255) | NO | | NULL | |
| fk_user_author | int(11) | YES | | NULL | |
| fk_user_modif | int(11) | YES | | NULL | |
| active | tinyint(4) | NO | | 1 | |
+----------------+--------------+------+-----+-------------------+-----------------------------+
13 rows in set (0.00 sec)


My MySQL table is not empty. There is already data and I want to add my .sql file after my data table.

I didn't execute this command because I think it's false :

LOAD DATA LOCAL INFILE 'data_3.9.sql'
INTO TABLE llx_accounting_account
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'


Do you have the solution ?

Thank you :)

----------------------------------------------------------------------------------



Solution :

With comments by @RakeshKumar and @PaulF, I found a way to solve my problem :

1) I deleted all rows where
fk_pcg_version = 'SYSCOHADA'
:

delete from llx_accounting_account where fk_pcg_version = 'SYSCOHADA' ;


2) I imported the .sql file :

mysql -u root -p****** dolibarr < data_3.9.sql


3) I modified one information because
account_number
was 1 instead of 10 where
rowid = 15000
:

UPDATE llx_accounting_account SET account_number = 10 WHERE rowid=15000 ;


Seems good :)

Thank you ;)

Answer

Use following way to import file

mysql -u username -p'password' dbname < filename.sql

Your import didn't work because you had already your same old SYSCOHADA rows in your table.

You can delete all rows where fk_pcg_version = 'SYSCOHADA' and import again your file corrected.

Comments