SQL Drip 32-excel concatenate function generates SQL statement

Source: SQL Drip 32-excel concatenate function generates SQL statement

When you get an Excel, you need to insert this data into the database, what to do, in addition to using the SSIS data import can also use the CONCATENATE function in Excel, this is a bit tricky, the first time you use may be a bit confusing. It's not difficult if we understand the definition of this function.

Defined

The CONCATENATE function merges up to 255 text strings into a single text string. Join items can be text, numbers, cell references, or a combination of these items. For example, if your worksheet contains a person's first name in cell A1, and cell B1 contains the last name of the man, you can merge the two values into another cell by using the following formula: =concatenate (A1, "", B1) the second parameter in this example ("") is a space character. You must specify any space or punctuation that you want to appear in the results as a parameter enclosed in double quotation marks.

Grammar
Concatenate (Text1, [Text2], ...) The CONCATENATE function syntax has the following parameters (parameters are: values that provide information for operations, events, methods, properties, functions, or procedures). ）：
Text1 required. The first text item to connect.
Text2, ... Optional. Other text items, up to 255 items. Items must be separated from items by commas.

Note: You can also use the join symbol (&) calculation operator instead of the CONCATENATE function to concatenate text items. For example, =A1 & B1 return the same value as =concatenate (A1, B1)

Example

The Excel content is as follows:

NSRBM MC Gly
001 A1 Xiao Li
002 A2 Xiao Wang
003 A3 Small Three
004 A4 Small Four

First look at the following expression:

=concatenate ("INSERT into Daoru (nsrbm,mc,gly) VALUES ('", A2, "', '", B2, "', '", C2, "');")

This preferred expression is preceded by a "=", then the expression name concatenate (), and finally its argument, which is the most complex.

The first parameter: "INSERT INTO Daoru (nsrbm,mc,gly) VALUES ('" This is a string

Second parameter: A2 This is a cell reference

The third argument: "', '" This is a string

Fourth parameter: B2 This is a cell reference

The fifth parameter: "', '" This is a string

Sixth parameter: C2 This is a cell reference

Seventh parameter: "');" This is a string

The last statement generated is as follows:

Insert into Daoru (nsrbm,mc,gly) VALUES (' 001 ', ' A1 ', ' Xiao Li ');
Insert into Daoru (nsrbm,mc,gly) VALUES (' 002 ', ' A2 ', ' Xiao Wang ');
Insert into Daoru (nsrbm,mc,gly) VALUES (' 003 ', ' A3 ', ' small Three ');
Insert into Daoru (nsrbm,mc,gly) VALUES (' 004 ', ' A4 ', ' small Four ');

SQL Drip 32-excel concatenate function generates SQL statement