How to import and export data in SQL Server?

Data import and export will be used when SQL Server programming or management is involved. There are many ways to import and export data, which can be summarized based on my experiences in Project Creation:

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:

 
 

  
  
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 the DataSet and able
  
  
And IDataReader.
  
  
If (dt. Rows. Count> 0)
  
  
Foreach (DataRow dr in dt. Rows)
  
  
{
  
  
SQLstmt = "insert into MyTable values ('" + dr ..."
 
 

3. BCP can be used for importing and exporting large data volumes.

Syntax:

 
 

  
  
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:

 
 

  
  
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: the first data row

ROWTERMINATOR: the row Terminator.

For example:

 
 

  
  
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:

 
 

  
  
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:

 
 

  
  
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:

 
 

  
  
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 (linked_server ,'query') 
 
 

For example:

 
 

  
  
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 just a summary of some DB data import and export methods and some simple examples, hope to help you in practice.

Original article title: SQL Server data import and export Methods

Link: http://www.cnblogs.com/changbluesky/archive/2010/06/23/1761779.html