When implemented in software projects, import data project personnel has been a troublesome issue. In fact, a lot of SQL Server in integrated batch import data. Some of the project implementation consultants headache, in the eyes of our database administrator, a piece of cake. The focus now is how to allow users to understand the ways in which a number of data import easier.
The first method: using the Select Into Statement
If the enterprise database is used by all SQL Server database, then you can use Select Into statements for data import. Select Into statements, his role is to data from another database query out, and then added to a user specified table.
In use, when this statement needs to pay attention to several aspects.
One purpose of the database needs to first create the related table. If want to Invoicing System database (SQLServer) in the product information sheet (Product) imported into the ERP system product information sheet (M_Product) in. Early in the ERP system is a database already set up this product information sheet.
Second, this method only copy data in the table, copy the table without an index. Invoicing System data such as product information sheet, product number, product type and other fields to create the index. Select Into statements are used to copy data to the ERP system, the time table, just copy the data content itself, and not copy the index and other information.
Third, the limitations of the use of this statement. In general, only be able to use SQL Server database. However, different versions of SQL Server databases, such as 2008 or 2003, also are compatible. If you need to import the object database is not SQL Server, you need to use other methods.
Fourth, with this statement, then the destination must not exist in the data table. Otherwise, the purpose of data in the table will be cleared. In other words, this statement does not support tables and table data consolidation. In SQL Server, there is a similar statement, you can achieve this function. This statement is: Insert Into. His role is to convert the data into a table to the current table. If the user wants the time table and the table data merge, you can use this statement. Both can not be confused with the use of otherwise easily lead to data loss.
Fifth, these two statements are in support of different types of compatible data types. If the original targets in a field data type is integer type, but in the destination table in the field of data type is floating-point type, as long as the two already compatible data types, then import the time database is allowed.
The second method: using Excel and other tools to control the middle
Although the first method is simple to operate, but its also has some disadvantages. If he only supports the same type of database; not able to interfere too much data and so on. In general, if the user of the original data, higher accuracy, without too much modification can be used to use, then the author had used the first approach.
However, if the original database, the accuracy of the data is not very high, or, a lot of data is scrapped. In short, the need to collate data from the original database to be able to use the situation, I do not propose to go into first, and then make changes. I encountered such a situation, like the use of Excle as an intermediate tool. That is, first data lead to Excle the Central Plains in the database. Some databases, such as the Oracle database, he does not support Excle format. However, we can guide it to CSV format. This file can be played Excle open.
Then, in Excle in the record to be modified. As Excle is a strong form processing software, so the data changes than the direct changes in the database is more convenient and more simple. If you can use functions such as sorting by time, some records do not clear out a long time. Can also use the replacement functions, some non-standard characters to change out. The original in the database of the more complex tasks, in Excle other tools can easily be completed.
Changes until the contents of the table is correct, the database administrator can Excle table files directly into SQL Server database. Because SQL Server and Excel are the same parents, so, good compatibility between them. In Sql Server provides directly import data from Excel file in the tools.
While this should help the middle Tool to import data, but, because of its handling up convenient, intuitive, so I use this all the time in most ways.