The perfect combination of row-to-column--excel and database

has been in the job for some time now, in addition to constantly improve the new features, online operation is also a very important task, every day needs to occupy a

Quantitative time to deal with, how to solve these problems efficiently and accurately is worth considering.

Today, the customer service staff gave me feedback on such an online problem, the details see:

The above employee code value in the child table cannot be found in the primary table. Assuming that we are now out of the real world, we simply delete these employees ' information from the sub-table.

In addition, such a bunch of data first think about how you will deal with it??

Share my solution below. If you have a better idea, you are welcome to comment in the comments below or email me to share:

[Email protected]

Here I just take some of the data as an example to reproduce it, the simple idea is to use Excel table for row to column operation, and then in the SQL statement

In do batch processing. Here's how it's done:

1, copy the above employee code, paste into Excel table A1

、

2. Click on the menu bar [data]-->[breakdown]

3, the data is divided into 3 steps, in the 2nd step set the separator symbol standard, here is ",", pay attention to the Chinese and English format, in the data preview can see the effect after separation

A fruit chart. The next step is complete.


Employee code after the breakdown

4, copy the data row, in the blank grid right click on the popup shortcut menu to find--[paste], in the expanded content select [Transpose]

After row to column:

5, to here is not over Oh, continue to see. Insert one row left and right in the data column, note: Enter a space first, then the English format

"'", about the same.

6. Copy the above cell data into the database, replace it with [ctrl+h] all the spaces between the ' ... ' and then use in bulk Delete.

DELETE TestA WHERE Employeecode in (
' 05c41401-0dc0-4690-bb00-848f2a99448b ',
' 064149f7-202b-44b0-81ec-1b2c8e745082 ',
' 0858876b-7222-4be8-8d4d-49b4f5dcaf7d ',
' 0a7d458f-3344-4340-8b0e-04220aaececd ',
' 0e0cea72-0156-40fe-82c6-a7cc36ae7c8f ',
' 0FCA3044-CC3F-48A6-A7B0-2474D4388ECC ',
' 14521fa3-79c7-4c3b-bcd1-0dd814588665 ',
' 15ebb333-a773-a1fd-41c2-46135933d1b9 ',
' 1624b1f7-e03e-4b97-8b19-e38198cbb644 ',
' 1685227e-0385-46ae-93b0-ea3031685e71 ',
' 22ba9ec1-ca83-46be-a3ae-ef82f52ea3d8 ',
' 2377bc30-1bb8-4126-a621-6f8ec2e9d8de '
) and Versionendtime is NULL

The problem will be solved here. The combination of Excel and the database has greatly reduced our workload. If at this time you have a better idea or build

, with Time Huan Welcome to share!

Summary of--------------------------------------------thought--------------------------------------------------

The technology is precious, the thought price is higher. A good programmer, efficient development is not by time piled up, thinking move Move Down, will

On the road of programming, the farther, the higher the climb.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The perfect combination of row-to-column--excel and database