When the software project is implemented,Data import has always been a headache for project staff.In fact, many methods for importing data in batches are integrated in SQL Server.Some project implementation consultants have headaches,In the eyes of our database administrators,It's a piece of cake.The point now is,How to make users understand these methods,Make data import easier.I believe everyone has used the following methods,Knowing the new Kazakhstan,If there is a better way, I hope everyone will propose it ~
First, use the select into statement
If the enterprise database uses the sql server database,You can use the select into statement to implement data import.select into statement, his role is to query the data from another database,Then join to a user-specified table.
When using this statement,Need to pay attention to several aspects.
1. You need to create related tables in the destination database.If i want to import the product information table (product) in the inventory system database (sqlserver) into the product information table (m_product) in the erp system. The product information table has been established in the database of the erp system in the early stage.
2. This method only copies the data in the table.Without copying the indexes in the table.For example, in the product information table in the inventory system data,Indexed on the product number, product category, and other fields.When using the select into statement to copy data to a table in the erp system,Just copied the data content itself,It does not copy information such as indexes.
3. The use of this statement is limited.In general,This can only be used in the sql server database.However, for databases of different versions of SQL Server,Such as 2008 or 2003, they are all compatible.If the object database to be imported is not sql server, other methods need to be adopted.
4. If you use this sentence,There must be no data in the destination table.if not,The data in the destination table is cleared.That is,This statement does not support merging of tables and table data.In sql server, there is a similar statement,Can achieve this function.This statement is:insert into. His role is to insert data from another table into the current table.If the user wants to merge the time table with the table data,You can use this statement.The two cannot be used confusingly,if not,It is easy to cause data loss.
5, the above two statements support compatible different types of data types.As in the original standard,The data type of a field is integer.But the data type of this field in the destination table is floating point,As long as the two data types are inherently compatible,When importing,The database is allowed.
One more tip:
When we want to copy all the field names of table a to a new table b, we can use the following method:
1) select * into b from a where 1<1 (only available for sql server)
2) select top 0 * into b from a
Control using intermediate tools such as excel
Although the first method is relatively simple to operate,But it also has some disadvantages.If he only supports the same type of database;Not being able to interfere too much with the data and so on.In general,If the accuracy of the user's original data is relatively high,If you can use it directly without too much modification,Then the author has adopted the first method.
However, if in the original database,The accuracy of the data is not very high,Or, a lot of data is scrapped.In short, the data of the original database needs to be organized.Before it can be used,I don't recommend importing it first,Make changes again.When I encountered this situation,Like to use excle as an intermediate tool.That is,First import the data from the original database into excle. Some databases,Such as oracle database, he does not support excle format. However, we can export it as a csv file.This file excle can also be opened.
Then, in excle, modify the record.Since excle is a very powerful form processing software,So, its data is modified,More convenient than modifying directly in the database,It's simple. If you can use functions such as sorting by time,Clear out some long-used records.You can also use functions such as replacement,Change some non-standard characters.These were more complex tasks in the database,Can be easily done in tools such as excle.
After the contents of the table are modified correctly,The database administrator can directly import the files in the excle table into the sql server database.Since SQL Server and Excel are born to the same parent,So, they are very compatible.A tool for directly importing data from an excel file is provided in SQL Server.
Although this involves importing data with the help of intermediate tools,However, because it is easy and intuitive to handle, I use it most of the time.Finally, attach the relevant code on how to make the excel table visible in the database:
exec sp_addlinkedserver "excel", "ex", "microsoft.jet.oledb.4.0", "c:\ documents and settings \ administrator \ desktop \ abc.xls", null, "excel 5.0" --create an excel Connection services,You can view the contents of abc.xls exec sp_addlinkedsrvlogin "excel", "false", "sa", "admin", null-create a mapping to allow sql server to log in sa log in with excel admin connect to excel without password --exec sp_dropserver "excel" --Delete the connection select * from excel ... sheet1 $-view the contents of the sheet1 sheet of excel
Tip:the data in the excel table,Spaces must be stripped first,Otherwise, it may be due to spaces,Inconsistent data.the name of the excel table,To be set to abc.xls and placed on the desktop.When opening the specified excel sheet,Its data cannot be viewed in the database,Otherwise the database will report an error.
Sometimes, there are too many choices,Users do not know how to start.I usually like the second method.It's more intuitive,In addition, you can batch change and organize your data.However, the disadvantage is that it is relatively inefficient.Especially when excle software handles a large number of records,It's slower.
So in the process of using,According to personal habits and preferences, choose the operation that you are proficient in,But we also need to analyze specific issues.
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- xcode - pod install [!] no `podfile 'found in the project directory
- dart - flutter: the instance member'stars' can't be accessed in an initializer error