Four methods to batch import data in the SQLServer Database

During the implementation of software projects, data import has always been a headache for the project staff. In fact, many data import methods are integrated in SQLServer. Some project implementation consultants have a headache. In the eyes of our database administrators, it is a piece of cake. The focus is on how to let users know about these methods and how to import data

During the implementation of software projects, data import has always been a headache for the project staff. In fact, many data import methods are integrated in SQL Server. Some project implementation consultants have a headache. In the eyes of our database administrator, it is a piece of cake. The focus is on how to let users know about these methods and how to import data

When implementing software projects,DataImportIt has always been a headache for project staff. In fact, it has been integrated into manyBatchImportDataOfMethod. Some project implementation consultants have a headache.DataLibraryIn the Administrator's eyes, it is a piece of cake. The focus is on how to let users knowMethod, LetDataImportIt is easier.
First, use the Select Into statement.
If the EnterpriseDataLibraryAll use SQL ServerDataLibraryYou can use the Select Into statement to implementDataOfImport. The role of the Select Into statement isDataFrom anotherDataLibraryAnd then add it to the table specified by a user.
When using this statement, pay attention to several aspects.
First, it is necessaryDataLibraryCreate related tables first. For exampleDataLibraryProduct Information table in (SQLServer)ImportTo the product information table (M_Product) in the ERP system. In the early stageDataLibraryThe product information table has been created.
SecondMethodOnly copyDataInstead of tabulation indexes. For example, in the invoicing systemDataIn the product information table, an index is created on the product number, product type, and other fields. The Select Into statementDataWhen copying data to the ERP system tableDataInstead of copying the index and other information.
Third, this statement has limitations. GenerallyDataLibrary. However, for SQL Server versionsDataLibrarySuch as 2008 or 2003, are also compatible. If you needImportObjectDataLibraryOtherMethod.
4. if this statement is used, it must not exist in the target table.Data. OtherwiseDataWill be cleared. That is to say, this statement does not support tables and tables.Data. In SQL Server, a similar statement can be used to implement this function. This statement is Insert. Its role isDataInsert to the current table. If you wantDataYou can use this statement. The two cannot be confused. Otherwise, it is easy to causeData.
5. the preceding two statements support different types of compatibility.DataType. For example, in the original tagDataThe type is integer, butDataThe data type is floating-point.DataIf the type is compatibleImportWhen,DataLibraryYes.
Second, use Excel and other intermediate tools for control
Although the firstMethodThe operation is relatively simple, but it also has some disadvantages. For example, it only supports the same typeDataLibrary; CannotDataToo many interventions. GenerallyDataIf the accuracy is relatively high and it can be used directly without too many modifications, the first method is adopted for the examination.
HoweverDataLibraryMedium,DataThe accuracy is not very high, or there are manyDataIs decommissioned. In short, you needDataLibraryOfDataFor more informationImportAnd then make changes. In this case, I like to use Excle as an intermediate tool. That is to sayDataCentral PlainsDataLibraryTo Excle. SomeDataLibrarySuch as OracleDataLibrary. It does 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. Because Excle is a powerful table processing software, itsDataModifyDataLibraryIt is easy to directly modify. 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 areDataLibraryAnd can be easily completed in tools such as Excle.
After the table content is modified correctly,DataLibraryThe administrator can directly copy the files in the Excle table.ImportTo SQL ServerDataLibrary. Since SQL Server and Excel are the same parent, they have good compatibility. SQL Server providesImportData.
Although this requires the use of intermediate toolsImportDataHowever, because it is convenient and intuitive to handle, this method is usually used in most cases.
Third: UseDataConversion ServiceImportData
　　DataThe conversion service is SQL Server.DataLibraryProvides a very powerful tool. In SQLServer,DataThe conversion function has a graphical user interface, which allows youImportData, AndDataEdit the file.
In addition,DataThe conversion service also supports programming interfaces of COM components. This means that you can directly callDataConversion service. Allows users to use the foreground application system instead of the backgroundDataLibraryIf the system performs any operation, you canDataImportDataLibraryGo to the system. In front-endDataLibrarySystemImport, There is an obvious benefit, You can firstDataTo check the validity. For example, you can use a script language such as VBDataTesting, purification, and certain conversion for the purposeDataLibrary.
For example, in the employee information tableDataLibraryIn the system, 0 or 1 may be used to indicate marital status. 0 indicates unmarried, 1 indicates married. In SQL ServerDataLibraryY or N is used to indicate the marital status. Y indicates married, N indicates unmarried. InImportDataWhen the OracleDataLibraryTableDataImportTo SQL ServerDataLibraryBecause the Marital Status field stores different types of content, it cannot be directly exported. In this case, you canImportDataPreviouslyDataType. If not, you can use the script languageDataConverts 0 to N, 1 to Y, and so on.
Therefore, when developers develop front-end applicationsDataImportWe recommend that you use this function.DataConversion service. There are not only ready-made interfaces, but alsoDataVerification and a certain degree of conversion. In addition,DataConversion ServiceDataImportHigh efficiency. Even if it is called through the foreground program, its performance is better than otherMethodUnder the same conditions, a higher level is required. In additionDataIncrease in volume,DataThe advantages of the conversion service will become more and more obvious.
However, the front-end application callsDataPay attention to the conversion service.DataThe COM interface provided by the conversion service is complicated, so the foreground program callsDataThe code of the conversion service is also complicated. If some scripting languages are added, the processing may be more complicated. Therefore, this interface is generally used only on large systems. IfDataNot much; otherwise, complex verification and conversion are not required. Using this interface is not worth the candle.
Fourth: heterogeneousDataLibraryBetweenImportExport
Although both the second and third methods can be heterogeneousDataLibraryBetweenDataOfImportExport a job. However, SQL Server provides another solution. That is, directly on SQL ServerDataLibraryTo connect to other typesDataLibraryAnd then use the Select Into statement.DataOfImportJob.
In SQL Server, two functions are provided to help us implement non-SQL ServerDataLibrary. The two functions are Opendatesource and Openrowset respectively. Their functions are basically the same, but they differ in details.
For example, Opendatesource can be used as an open source function.DataLibraryBut cannot filter tables and views. If you only wantDataImportTo SQL ServerDataLibraryYou cannot directly filter the source table. The filtering action must be performed on SQL ServerDataLibrary. The Openrowset function can be used to openDataLibraryWhen using a table or view, you can use the Where clause to filter records. Therefore, Openrowset is frequently used in practical applications.
However, because it requires users to write complex parameters, and cannot provide complexDataThe verification function is not used much in actual work. In some small application systems, we can see it occasionally. This method is rarely used in some mature commercial software.DataProceedImport.
Sometimes, the user does not know how to start when there are too many options. The second method is usually used for examination. It is intuitive and canDataProceedBatch. However, the defect is that the efficiency is relatively low, especially when Excle software processes a large number of records, the speed is relatively slow. If this method does not work, the exam is more likely to be usedDataThe conversion method. This operation is complex, but it can provide more complex verification, and can be used in a graphical interfaceDataIt is highly efficient.


Ps: article from http://www.233.com/ncre/three/db/fudao/20090225/084918877.html