Excel data generation SQL INSERT statement

Excel table has a, B, c Three column data, you want to import into the Database Users table, the corresponding fields are name,sex,age.

Add a column to your Excel table and use Excel's formulas to generate SQL statements automatically, as follows:

1. Add a column (column D)

2, in the first row of column D, is D1 input formula: =concatenate ("INSERT into users (name,sex,age) VALUES ('", A1, "', '", B1, "', '", C1, "');")

3. At this point, D1 has generated the following SQL statement: "INSERT into Users (name,sex,age) VALUES (' ls ', ' female ', ' 24 ')";

4. Copy the D1 formula to column D of all rows

5. At this point, the D column has generated all the SQL statements

6. Copy d column to a plain text file

7. Remove the double quotation marks from the SQL statement

=============================== Gorgeous split-line =============================================

We often encounter such requirements: the user to send some data, we directly to the database, there are some inserts, some update and so on, a small amount of data we can take the most primitive way, that is, in SQL with insert into to achieve, But if there are dozens of hundreds of or even thousands of data on the time to continue to write a separate SQL statement, it is miserable, in fact, there are two simple methods;

First, after the Excel data has been collated, the SQL import function directly into the database, but to ensure that the database fields and Excel fields consistent.

Second, through Excel to generate the corresponding SQL statement, directly copy the SQL statement into the parser inside the execution can, this article said how to achieve this second approach.

First of all, our goal is to insert these 20 data into the database, a two words can write their own insert statement, here are 20 data, can not fully handwritten 20 statements out of it,

Obviously, can not write one by one of the SQL, too many, there are only 20, if it is 200, 2000 data?

INSERT into TableName (column1,column2,column3) VALUES (' Value1 ', ' Value2 ', ' Value3 ')

After writing a statement, directly from the beginning to the end, you will find that all the data have a corresponding script, this time you can directly copy to the parser, click "F5", OK, your task is completed.

Because in the formula, so sometimes those statements will change, when you generate these statements, you can choose to paste the values, and then put into SQL to execute, as follows:

This article was reprinted from: 53437908

Excel data generation SQL INSERT statement