SQL Drip 32-excel concatenate function generates SQL statement

Source: Internet
Author: User

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.


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.

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)


The Excel content is as follows:

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.