1. SQL Server Import Export Wizard, this is the most convenient way.
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, and so on, basically to meet the requirements of system development.
The Export Wizard also has the same number of destination source drivers that can import data to different destination sources.
This is easy to do for database administrators, and SQL Server will help you create a table of the same structure when you import it.
2. Code implementation with. NET (for example, a txt or Excel file, to read to DB)
2.1 The most common is to read the contents of TXT, and then a piece of the text into the table. No more details here.
2.2 Integrated read, using OLE DB driver.
The code is as follows:
Copy Code 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 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 the dataset to match the 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, can be used as a large capacity data import export, also can cooperate with xp_cmdshell to use.
Grammar:
BCP syntax
Copy Code code 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 of the data import export: In,out,queryout
Such as:
Such as:
4.BULK INSERT. T-SQL command to allow direct import of data
Grammar:
Copy Code 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 ' | ' 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: First Data row
Rowterminator: Line non-terminal
Such as:
Copy Code code as follows:
BULK INSERT dbo. Importtest
From ' C:\ImportData.txt '
With (FieldTerminator = ', ', firstrow = 2)
5. OpenRowset is also a T-SQL command, and the information that contains the DB connection is different from other import methods, OPENROWSET can participate in INSERT,UPDATE,DELETE operations as a target table.
Grammar:
Copy Code code 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]
Such as:
Copy Code code 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 are not NULL)
6.OPENDATASOURCE
Grammar:
OpenDataSource (provider_name,init_string)
Such as:
Copy Code 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 that executes on the basis of linked server. So the link server must be set up before executing. The OPENQUERY result set can be used as a table to participate in DML operations.
Grammar:
OPENQUERY (linked_server, ' query ')
Such as:
Copy Code 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$] ')
The above is a simple summary of some of the DB data import and export methods and some simple examples, I hope it will help you in practice.
