How to integrate multiple batch data import methods in SQLServer

Source: Internet
Author: User
During the implementation of software projects, data import has always been a headache for the project staff. If you want to import product information from the existing inventory management system to the ERP system, it is unrealistic for enterprise users to manually enter the information. In fact, many batch data import methods have been integrated in SQLServer. Some project implementation consultants have a headache

During the implementation of software projects, data import has always been a headache for the project staff. If you want to import product information from the existing inventory management system to the ERP system, it is unrealistic for enterprise users to manually enter the information. In fact, many batch data import methods have been integrated in SQL Server. Some project implementation consultants have a headache

During the implementation of software projects, data import has always been a headache for the project staff. If you want to import product information from the existing inventory management system to the ERP system, it is unrealistic for enterprise users to manually enter the information.

In fact, many batch data import methods have been integrated in SQL Server. Some project implementation consultants have a headache. In the eyes of our administrators, it is a piece of cake. The focus is on how to make data import easier by letting users understand these methods.

  Method 1: use Excel and other intermediate tools for control

Although the first method is relatively simple, it also has some disadvantages. For example, it only supports the same type of database, and cannot perform too many data interventions. Under normal circumstances, if the user's original data is relatively accurate and can be used directly without too many modifications, the author has adopted the first method.

However, in the original database, the data accuracy is not very high, or a lot of data is decommissioned. In short, you need to sort out the data of the original database before you can use the data. I do not recommend that you import the data first before making changes. In this case, I like to use Excle as an intermediate tool. That is to say, first import the data from the original database to the Excle. Some databases, such as Oracle databases, do not support the Excle format. However, we can export it to a CSV file. This type of file Excle can also be opened.

Then, modify the record in Excle. As Excle is a powerful table processing software, it is easier to modify data than to directly modify data in a database. For example, you can use time-based sorting to clear records that are not used for a long time. You can also use replacement and other functions to change some nonstandard characters. These previously complex tasks in the database can be easily completed in tools such as Excle.

After the table content is modified correctly, the database administrator can directly import the files in the Excle table to the SQL Server database. Since SQL Server and Excel are the same parent, they have good compatibility. SQL Server provides a tool to import data directly from an Excel file.

Although it is necessary to use an intermediate tool to import data, because it is convenient and intuitive to process, I usually use this method most of the time.

  Method 2: Import and Export between heterogeneous databases

Both the second and third methods can import and export data between heterogeneous databases. However, SQL Server provides another solution. That is, directly connect to other types of databases in the SQL Server database, and then use Select Into and other statements to import data.

In SQL Server, two functions are provided to help us connect non-SQL Server databases. The two functions are Opendatesource and Openrowset respectively. Their functions are basically the same, but they differ in details.

For example, the Opendatesource function can be used to create tables and views of open source databases, but cannot be used to filter them. If you only want to import some data from the source table to the SQL Server database table, you cannot directly filter the source table. The filtering action must be performed in the SQL Server database. The Openrowset function can directly filter records by using conditional restrictions such as Where when opening the tables or views of the target database. Therefore, Openrowset is frequently used in practical applications.

However, because it requires users to write complex parameters and does not provide complex data verification functions, not many are used in actual work. In some small application systems, we can see it occasionally. This method is rarely used in some mature commercial software to import data.

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.