SQL Server high-speed import data sharing

SQL Server imports data at high speed, such as the following methods: CTE, Openrowset/opendatasource, BULK INSERT, bcp, Shell.

These methods are described in turn.

1.CTEFirst of all. Let's see what a CTE is.

A common table expression (Common table expression) is an attribute introduced after the SQL SERVER 2005 version number.

The CTE can be seen as a temporary result set that can be referenced multiple times in the next Select,insert,update,delete,merge statement. Use common expressions to make statements clearer and more concise.

A CTE is similar to a derived table where the detail table is not stored as an object and is only valid for the duration of the query. is different from a derived table. The CTE can be self-referenced and can be referenced more than once in the same query.
Many others click: http://technet.microsoft.com/zh-cn/library/ms190766 (v=sql.105). aspx


The scale is as follows:

Use ADVENTUREWORKS2008R2; go--Define the CTE expression name and column list. With Sales_cte (SalesPersonID, SalesOrderID, salesyear) as--Define the CTE query. (    SELECT SalesPersonID, SalesOrderID, Year (OrderDate) as salesyear to    #temp1    from Sales.SalesOrderHeader    WHERE SalesPersonID is not NULL)--Define The outer query referencing the CTE name. SELECT SalesPersonID, COUNT (SalesOrderID) as TotalSales, Salesyearinto #temp2FROM sales_ctegroup by Salesyear, Salespersonidorder by SalesPersonID, Salesyear; GO

2.openrowset/opendatasourceOpenRowset and OPENDATASOURCE are able to access remote databases, but in detail they are still different. OpenDataSource does not use the linked server name, but provides special connection information as part of the four-part object name. The OPENROWSET includes all the connection information required to access remote data from the OLE DB data source.

This approach is an alternative to visiting tables in the linked server, and is a one-time, special way to connect and access remote data using OLE DB. The OpenRowSet function can be referenced in the FROM clause of the query as if it were a reference table. Based on the ability of the OLE DB provider. You can also refer to the OPENROWSET function as the target table for an INSERT, UPDATE, or DELETE statement. Although a query may return multiple result sets, OPENROWSET only returns the first. Many others please click: http://technet.microsoft.com/en-us/library/ms179856.aspx


The scale is as follows:

--Enable ad hoc distributed queriesexec sp_configure ' show advanced options ', 1RECONFIGUREEXEC sp_configure ' ad hoc distributed Queries ', 1reconfigure--import data using OPENDATASOURCE INSERT INTO IMP_DATA.dbo.t_goodsSELECT * from OpenDataSource (' microsoft.jet.oledb.12.0 ', ' Data source= ' e:/report1.txt '; User id=admin; password=; Extended properties=excel 12.0 ') ... [When you are finished using sheet1$]--, remember to close it.] Since this is a security risk exec sp_configure ' Ad Hoc distributed Queries ', 0RECONFIGUREEXEC sp_configure  

3.BULK INSERT

BULK Insert agrees to import the data file into a database table or view in the format specified by the user. Many others please click: http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

the scale is as follows:

--Define import purpose and import source bulk INSERT IMP_DATA.dbo.t_goods from ' E:/report1.txt ' with (   --column delimiter  fieldterminator = ', ',  -- Line delimiter  rowterminator = ' \ n '  )

4.bcpThe bcp useful tool enables bulk copying of data between Microsoft SQL Server instances and data files in user-specified formats. Using the bcp utility, you can import a large number of new rows into a SQL Server table. or export the table data to a data file. Unless used with the queryout option, there is no need to understand Transact-SQL knowledge with this useful tool. To import data into a table, you must use a format file created for the table, or you must understand the structure of the table and the data types that are valid for the columns in the table.

Many others please click: http://msdn.microsoft.com/zh-cn/library/ms162802.aspx


The scale is as follows:

--Turn on advanced options exec sp_configure ' show advance options ', 1; reconfigure;--enable run cmd command exec sp_configure ' xp_cmdshell ', 1; reconfigure;--Specify import Intent and import source exec master: xp_cmdshell ' BCP IMP_DATA.dbo.t_goods in E:\report.txt-c-T '

5.Shell
The way the shell inserts strings through stitching is very flexible and has fewer errors. But the inserted content will be very annoying if it includes very many illegal characters.

Be able to participate in an article that was written: lack of import data permissions, SQL Server creates test data

Finally, put a picture of the time before. Summary of import data:

Good luck!

SQL Server high-speed import data sharing