Summary of SQL Server data import and export Methods

1. SQL Server Import and Export wizard. This method 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, it can basically meet the requirements of system development.
The export wizard also has the same number of target source drivers that can import data to different target sources.
This method is simple and easy for database administrators. During import, SQL Server will also help you create tables with the same structure.
2. Use. NET Code (for example, there is a txt or excel file that is read to the database)
2.1 The most common is to read the txt content cyclically and insert one by one into the Table.
2.2 read the entire set using the OLEDB driver.

The Code is as follows:
Copy codeThe Code is 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 in. txt.
DataTable dt = new DataTable ();
OleDbDataAdapter da = new OleDbDataAdapter (SQLstmt, conn );
Da. Fill (dt); // Add or refresh rows in the specified range of DataSet to match rows in the data source using the DataSet, DataTable, and IDataReader names.
If (dt. Rows. Count> 0)
Foreach (DataRow dr in dt. Rows)
{
SQLstmt = "insert into MyTable values ('" + dr ..."

3. BCP, which can be used for large data import and export, or xp_mongoshell.
Syntax:

BCP syntax
Copy codeThe 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]"]

Note the direction parameters for data import and export: in, out, queryout

For example:

For example:

4. bulk insert. T-SQL command that allows direct import of data
Syntax:
Copy codeThe Code is as follows:
BULK INSERT
[Database_name. [schema_name]. | schema_name.] [table_name | view_name]
FROM 'data _ file'
[
(
[[,] BATCHSIZE = batch_size]
[[,] CHECK_CONSTRAINTS]
[[,] CODEPAGE = {'acp '| 'oem' | '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 parameters:
FIELDTERMINATOR, field separator
FIRSTROW: the first data row
ROWTERMINATOR: the row Terminator.
For example:



Copy codeThe Code is as follows:
Bulk insert dbo. ImportTest
FROM 'C: \ ImportData.txt'
WITH (FIELDTERMINATOR = ',', FIRSTROW = 2)


5. OPENROWSET is also a T-SQL command, which contains DB connection information and other import methods. OPENROWSET can be used as a target table for INSERT, UPDATE, and DELETE operations.
Syntax:
Copy codeThe Code is as follows:
OPENROWSET
({'Provider _ name', {'datasource '; 'user _ id'; 'Password'
| 'Provider _ string '}
, {[Catalog.] [schema.] object
| 'Query'
}
| BULK 'data _ file ',
{FORMATFILE = 'format _ file_path '[<bulk_options>]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB}
}) <Bulk_options >::=
[, 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:
Copy codeThe Code is as follows:
Insert into dbo. ImportTest
SELECT * from openrowset ('Microsoft. Jet. OLEDB.4.0 ',
'Excel 8.0; Database = C: \ ImportData.xls ', 'select * FROM [Sheet1 $] 'where A1 is not null)

6. OPENDATASOURCE
Syntax:
OPENDATASOURCE (provider_name, init_string)
For example:
Copy codeThe Code is 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. It is a query executed on the basis of the linked server. Therefore, before execution, you must set up the result set of link server. OPENQUERY to participate in DML operations as a table.
Syntax:
OPENQUERY (pai_server, 'query ')
For example:
Copy codeThe Code is as follows:
EXEC sp_addmediaserver 'portdata ',
'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 $] ')


The above is just a summary of some DB data import and export methods and some simple examples, hope to help you in practice.