SQL Server Quick import data sharing

SQL Server can quickly import data by using the following methods: CTE, OpenRowSet/OpenDataSource, BULK INSERT, bcp, and Shell.

The following describes these methods in sequence.

1. CTE first, let's look at what CTE is. Common Table expressions are a feature introduced after SQL SERVER 2005. CTE can be considered as a temporary result set and can be referenced multiple times in the following SELECT, INSERT, UPDATE, DELETE, and MERGE statements. The use of public expressions can make statements clearer and more concise. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query.
For more information, click: http://technet.microsoft.com/zh-cn/library/ms190766 (v = SQL .105). aspx


Example:

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    INTO #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. Both OpenRowSet/OpenDataSourceOpenRowSet and OpenDataSource can access remote databases, but the performance of the two is different. OpenDataSource provides special connection information without using the linked server name and uses it as part of the four-part object name. OpenRowSet contains all the connection information required to access the remote data in the ole db data source. This method is an alternative when accessing tables on the linked server. It is a one-time, special way to connect to and access remote data using ole db. The OpenRowSet function can be referenced in the FROM clause of the query as in the reference table. Based on the capabilities of the ole db provider, the OpenRowSet function can also be referenced as the target table of the INSERT, UPDATE, or DELETE statement. Although a query may return multiple result sets, OPENROWSET returns only the first result set. More CLICK: http://technet.microsoft.com/en-us/library/ms179856.aspx


Example:

-- Enable Ad Hoc Distributed QueriesEXEC SP_CONFIGURE 'show advanced options', 1 RECONFIGUREEXEC SP_CONFIGURE 'ad Hoc Distributed Queries ', 1RECONFIGURE -- use OpenDataSource to import data insert into partitions * FROM OpenDataSource ('Microsoft. jet. OLEDB.12.0 ', 'Data Source = "E:/Report1.txt"; User ID = Admin; Password =; Extended properties = Excel 12.0 ')... [Sheet1 $] -- disable it after use, because it is a security risk EXEC SP_CONFIGURE 'ad Hoc Distributed Queries ', 0 RECONFIGUREEXEC SP_CONFIGURE 'show advanced options', 0 RECONFIGURE

3. BULK INSERT

Bulk insert allows you to import data files to database tables or views in the specified format. More CLICK: http://msdn.microsoft.com/zh-cn/library/ms188365.aspx

Example:

-- Define the import destination and import source bulk insert IMP_DATA.dbo.t_goods FROM 'e:/Report1.txt 'WITH (-- column separator FIELDTERMINATOR =', ', -- Row separator ROWTERMINATOR =' \ n ')

4. The bcpbcp utility can replicate data in large capacity between a Microsoft SQL Server instance and a data file in a specified format. The bcp utility can be used to import a large number of new rows to SQL Server tables or export table data to data files. Unless used with the queryout option, you do not need to know about Transact-SQL when using this utility. To import data to a table, you must use the format file created for the table, or you must understand the table structure and the data types that are valid for the columns in the table.
More CLICK: http://msdn.microsoft.com/zh-cn/library/ms162802.aspx


Example:

-- Open the advanced options EXEC SP_CONFIGURE 'show advanced options', 1; RECONFIGURE; -- enable the Execute Command EXEC SP_CONFIGURE 'xp _ cmdshell', 1; RECONFIGURE; -- specify the import destination and import source EXEC master .. xp_mongoshell 'bcp IMP_DATA.dbo.t_goods in E: \ report.txt-c-t'

5. Shell
Shell concatenates and inserts strings in a way that is flexible and has fewer errors. However, if the inserted content contains many illegal characters, it will be annoying. See previous articles: SQL Server creates test data without the permission to import data.

Finally, paste a chart made some time ago and import the Data Summary:

Good Luck!