Shulou(Shulou.com)05/31 Report--

How to import and export data in SQLServer, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. SQL Server Import and Export Wizard, this way is the most convenient. Import wizard, Microsoft provides a variety of data source drivers, including SQL Server Native Cliant, OLE DB For Oracle,Flat File Source,Access,Excel,XML, etc., can basically meet the needs of system development. Similarly, the export wizard has the same number of destination source drivers that can import data to different destination sources. For database administrators, this method is simple and easy to operate, and SQL Server will also help you build the same structure of Table when importing. two。 Net code implementation (for example, there is a txt or excel file, to read into DB) 2.1 the most common is to loop read the contents of txt, and then one by one stuffed into the Table. I will not repeat it here. 2.2 set read as a whole, using OLEDB driver. The code is as follows: copy the code as follows: string strOLEDBConnect = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\ 1\; Extended Properties='text;HDR=Yes;FMT=Delimited'"; OleDbConnection conn = new OleDbConnection (strOLEDBConnect); conn.Open (); SQLstmt = "select * from 1.txt"; / / read the data DataTable dt=new DataTable () in .txt; OleDbDataAdapter da = new OleDbDataAdapter (SQLstmt, conn); da.Fill (dt) / / add or refresh rows in the specified range of DataSet to match rows in data sources that use DataSet, DataTable, and IDataReader names. If (dt.Rows.Count > 0) foreach (DataRow dr in dt.Rows) {SQLstmt = "insert into MyTable values ('" + dr... ")

3.BCP, can be used as a large volume of data import and export, can also be used with xp_cmdshell. Syntax: the BCP syntax copy code is as follows: bcp {[[database_name.] [schema].] {table_name | view_name} | "query"} {in | out | queryout | format} data_file [- mmax_errors] [- fformat_file] [- x] [- eerr_file] [- Ffirst_row] [- Llast_row] [- bbatch_size] [- ddatabase_name] [- n] [- c] [- N] [- w] [- V (70 | 80 | 90)] [- Q] [- C {ACP | OEM | RAW | code_page}] [- tfield_term] [- rrow_term] [- iinput_file] [- ooutput_file] [- apacket_size] [- S [server_ name [\ instance_name] [- Ulogin_id] [- Ppassword] [- T] [- v] [- R] [- k] [- E] [- h "hint [ .. n] "]

Please pay attention to the direction parameters of data import and export: in,out,queryout such as: 4.BULK INSERT. T-SQL command to allow direct import of data syntax: copy the code as follows: BULK INSERT [database_name. [schema_name]. | schema_name. ] [table_name | view_name] FROM 'data_file' [WITH ([[,] BATCHSIZE = batch_size] [[,] CHECK_CONSTRAINTS] [[,] CODEPAGE = {' ACP' | 'RAW' |' code_page'}] [[,] DATAFILETYPE = {'char' |' native' | 'widechar' |' widenative'}] [,] FIELDTERMINATOR = 'field_terminator'] [ FIRSTROW = first_row] [[,] FIRE_TRIGGERS] [[,] FORMATFILE = 'format_file_path'] [[,] KEEPIDENTITY] [[,] KEEPNULLS] [[,] KILOBYTES_PER_BATCH = kilobytes_per_batch] [[,] LASTROW = last_row] [[,] MAXERRORS = max_errors] [,] ORDER ({column [ASC | DESC]} [ . n]) [[,] ROWS_PER_BATCH = rows_per_batch] [[,] ROWTERMINATOR = 'row_terminator'] [[,] TABLOCK] [[,] ERRORFILE =' file_name'])]

Important parameter: FIELDTERMINATOR, field delimiter FIRSTROW: first data line ROWTERMINATOR: line Terminator such as: copy code as follows: BULK INSERT dbo.ImportTest FROM'C:\ ImportData.txt' WITH (FIELDTERMINATOR =',', FIRSTROW = 2)

5. OPENROWSET is also a command of T-SQL, which contains information about DB connections. Unlike other import methods, OPENROWSET can participate in INSERT,UPDATE,DELETE operations as a target table. Syntax: copy the code as follows: OPENROWSET ({'provider_name', {' datasource';'user_id';'password' | 'provider_string'}, {[catalog. ] [schema. ] Object | 'query'} | BULK' data_file', {FORMATFILE = 'format_file_path' [] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB}}):: = [, CODEPAGE = {' ACP' | 'OEM' |' RAW' | 'code_page'}] [, ERRORFILE =' file_name'] [, FIRSTROW = first_row] [, LASTROW = last_row] [, MAXERRORS = maximum_errors] [ ROWS_PER_BATCH = rows_per_batch]

For example, the copy code is as follows: INSERT INTO dbo.ImportTest SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',' Excel 8.0 BosDatabase IS NOT NULL C:\ ImportData.xls', 'SELECT * FROM [Sheet1 $]' WHERE A1 IS NOT NULL)

6.OPENDATASOURCE syntax: OPENDATASOURCE (provider_name,init_string) such as: copy the code as follows: INSERT INTO dbo.ImportTest SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',' Data Source=C:\ ImportData.xls;Extended Properties=Excel 8.0'). [Sheet1 $]

7.OPENQUERY. Is a query executed on the basis of linked server. Therefore, before execution, the result set of link server.OPENQUERY must be established so that it can participate in the operation of DML as a table. Syntax: OPENQUERY (linked_server, 'query') such as: copy the code as follows: EXEC sp_addlinkedserver' ImportData', 'Jet 4.0,' Microsoft.Jet.OLEDB.4.0','C:\ ImportData.xls', NULL, 'Excel 8.0' GO INSERT INTO dbo.ImportTest SELECT * FROM OPENQUERY (ImportData, 'SELECT * FROM [Sheet1 $]')

After reading the above, have you mastered the method of how to import and export data in SQLServer? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.