SQL Server Bi step by step 4-1 Merge data merge and merge join Components

Merge data 1 merge and merge join Components

The purpose of this chapter is to import the data in Excel to the database in the previous chapters, so that no duplicate data can be inserted, that is, only data that does not exist in the Database can be inserted. if the data in the Excel worksheet is the same as the primary key of the data in the database, an exception will occur when the data is inserted again. How can we achieve automatic update? That is to say, it automatically determines the data of the Data Source. If there is data in the database, update will be executed, and insert will be executed if there is no data in the database?
It may be called merging data. Here, merging data is different from directly using the merge component and the merge join component. First, let's look at these two components.

 

Merge component configuration
1. Create a new package named mergedata.
2. Drag a Data Flow task from the toolbox in the control flow. Edit data streams, drag two excelsources, edit excelsources, and select the product.xls file.
3. Drag two sort components for sorting.
 

Sort by productnumber Field

4. Add merge to merge the data.

 
5. Drag an audit component. After configuring the data flow, add three data viewers respectively.

 Configured Data Flow Diagram

6. debug. View the running effect.
 

 

We can see that merge data only merges data from two data sources, including duplicate data. Note that merge has the following features:
1. Data must be sorted prior to merge conversion. This may be accomplished by using sort conversion as above, or by specifying order by directly in the source connection. (You Need To test)
2. the metadata between the two paths of the merged data must be the same (test required)
3. It can only be used for merging two data sets. If you want to merge two or more data sets, you can select the Union all conversion (test required)

 

Merge join component configuration
1. Create a new package named mergedata2.
2. Drag two ole db sources and connect the two data sources to the product table and productinventory table respectively.
3. Drag two sort components to sort them in ascending order by the productid field. Product is input on the left.
 

Product sorting

4. Drag a merge join component, set the connection type to "left external connection" (left join, external, internal join and SQL), and select the columns to be output to the path:
 

5. Add three data viewers. Complete all configurations

 
6. Run

 

 

As you can see, the merge join component is used to connect two tables with foreign keys and set the required output columns during merging to display the merged table data. It serves as an inner or outer connection in SQL.
You can see the connection with SQL. Of course, if two inputs are in the same database, you can directly use the join operation at the OLE DB data level, but when you want to merge two different data sources, merge join can be used. (not quite clear ?)

P.s

 

Project Step1 --- 4 source code file: the version is SQL 2005. You must install the Excel application before running the code.

/Files/cocole/Step1-4Sql05.rar

 

 

Author: Wukong's Sky (tianma xingkong)
Source: http://www.cnblogs.com/cocole/
The copyright of this article is shared by the author and the blog Park. You are welcome to repost this article. However, you must retain this statement without the author's consent and provide a clear link to the original article on the article page. Otherwise, you will be held legally liable.