Excel can quickly generate and import SQL, and batch update the update database

Time:2022-6-20

Record the operation of updating Excel data to database table once

  • Background:
    Because it is a production environment, the operator is afraid to update other data, so it can only update according to the Excel data provided by him. Therefore, the following records are available

1、 There are many ways to import Excel data into MySQL database

  • Writing Python scripts
  • Write shell script
  • Generate SQL directly with Excel and execute it in Navicat
    Because I am not familiar with Python and shell, I don’t want to spend so much effort in learning, so I choose to use Excel to generate SQL directly

2、 Steps of importing data with Excel method

1. Create temporary table_ tmp

Method 1:Import and create tables directly using Navicat

Excel can quickly generate and import SQL, and batch update the update database

image.png

All the way to next, you need to enter the name of the new table

Excel can quickly generate and import SQL, and batch update the update database

image.png

Then select the table field, primary key, and import

Excel can quickly generate and import SQL, and batch update the update database

image.png

Law IICreate database tables manually

--Create table
create  table change_tmp(id varchar(50) primary key, balance decimal(20,4));

2. Generate SQL spliced with Excel

Generating data values with Excel

Excel can quickly generate and import SQL, and batch update the update database

image.png

Copy to nodepad

Excel can quickly generate and import SQL, and batch update the update database

image.png

–Generate batch insert statements with excel as long as the contents of brackets are generated, such as:

=CONCATENATE("('",A2,"',",B2,"),")  

You can generate bracket contents (‘dd’, 23.2) with A2 and B2 as data sources. Cells need to be identified with double quotation marks and commas, such as “B2,”

--Insert the last generated SQL of the table
insert into change_tmp values  ('dd',23.2), ('d2d',1.2);

3. Data execution insert

insert into table_tmp values  
('dd',23.2),
('d2d',1.2);

4. Perform batch update

update dest a, change_tmp b set a.balance = b.balance where a.id=b.id;