SQL Server Import and Export

I found some information on the Internet, which is suitable for favorites:

/****** Export to excel
Exec master.. xp_mongoshell 'bcp settledb. DBO. shanghu out c: \ temp1.xls-C-Q-s "gnetdata/gnetdata"-U "sa"-P ""'

/*********** Import Excel
Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C: \ test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions

/* Dynamic file name
Declare @ FN varchar (20), @ s varchar (1000)
Set @ fn = 'C: \ test.xls'
Set @ s = '''microsoft. Jet. oledb.4.0 '',
''Data source ="'+ @ FN +' "; UserId = admin; Password =; extended properties = Excel 5.0 '''
Set @ s = 'select * From OpenDataSource ('+ @ S +')... sheet1 $'
Exec (@ s)
*/

Select cast (subject number as numeric (255) as nvarchar () + ''converted alias
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C: \ test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions

/*********************** Export to remote SQL
Insert OpenDataSource (
'Sqlodb ',
'Data source = remote IP address; user id = sa; Password = password'
). Database Name. DBO. Table Name (column name 1, column name 2)
Select column name 1, column name 2
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "C: \ test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions

/** Import a text file
Exec master .. xp_mongoshell 'bcp dbname .. tablename in C: \ dt.txt-C-sservername-USA-ppassword'

/** Export a text file
Exec master .. xp_mongoshell 'bcp dbname .. tablename out c: \ dt.txt-C-sservername-USA-ppassword'
Or
Exec master .. xp_mongoshell 'bcp "select * From dbname .. tablename" queryout c: \ dt.txt-C-sservername-USA-ppassword'

Export to TXT text, separated by commas
Exec master.. xp_mongoshell 'bcp "database name... table name" out "D: \ tt.txt"-c-t,-U sa-P password'

Bulk insert database name... table name
From 'C: \ test.txt'
With (
Fieldterminator = ';',
Rowterminator = '\ N'
)

--/* Dbase iv File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase IV; HDR = no; IMEX = 2; database = c: \ ', 'select * from [customer profile 4.dbf]')
--*/

--/* Dbase iii File
Select * from
OpenRowSet ('Microsoft. Jet. oledb.4.0'
, 'Dbase III; HDR = no; IMEX = 2; database = c: \ ', 'select * from [customer profile 3.dbf]')
--*/

--/* FoxPro Database
Select * From OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = c :\',
'Select * from [AA. DBF] ')
--*/

/************** Import the DBF File ****************/
Select * From OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver;
Sourcedb = E: \ vfp98 \ data;
Sourcetype = DBF ',
'Select * from customer where country! = "USA" order by country ')
Go
/**************** Export to DBF ***************/
If you want to export data to the generated structure (that is, the existing table) FoxPro table, you can directly use the following SQL statement

Insert into OpenRowSet ('msdasql ',
'Driver = Microsoft Visual FoxPro driver; sourcetype = DBF; sourcedb = c :\',
'Select * from [AA. DBF] ')
Select * from table

Note:
Sourcedb = c: \ specifies the folder where the Foxpro table is located
AA. DBF specifies the name of The FoxPro table.

 

********************/
Insert into OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'X: \ A. mdb '; 'admin'; '', table A) Select * from database name... Table B

/************* Import access ********************/
Insert into B Table selet * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ',
'X: \ A. mdb '; 'admin'; '', Table)

The file name is a parameter.
Declare @ fname varchar (20)
Set @ fname = 'd: \ test. MDB'
Exec ('select A. * From OpenDataSource (''microsoft. Jet. oledb.4.0 '',
''' + @ Fname + '''; ''admin '';'''', Topics) as ')

Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "F: \ northwind. mdb"; Jet oledb: Database Password = 123; user id = admin; Password =; ')... product

* ********************* Import XML files

Declare @ IDOC int
Declare @ Doc varchar (1000)
-- Sample XML document
Set @ Doc ='
<Root>
<Customer cid = "C1" name = "Janine" city = "issaquah">
<Order OID = "O1" date = "1/20/1996" amount = "3.5"/>
<Order OID = "O2" date = "4/30/1997" amount = "13.4"> customer was very satisfied
</Order>
</Customer>
<Customer cid = "C2" name = "Ursula" city = "oelde">
<Order OID = "O3" date = "7/14/1999" amount = "100" note = "wrap it blue
White red ">
<Urgency> important </urgency>
Happy customer.
</Order>
<Order OID = "O4" date = "1/20/1996" amount = "10000"/>
</Customer>
</Root>
'
-- Create an internal representation of the XML document.
Exec sp_xml_preparedocument @ IDOC output, @ Doc

-- Execute a SELECT statement using openxml rowset provider.
Select *
From openxml (@ IDOC, '/root/customer/order', 1)
With (OID char (5 ),
Amount float,
Comment ntext 'text ()')
Exec sp_xml_removedocument @ IDOC

/********************** Export to TXT ************* ***************************/
Intended Use
Select * into OpenDataSource (...) from OpenDataSource (...)
Import an Excel file to a text file

Suppose there are two columns in Excel, the first column is name, and the second column is a very row account (16 digits)
The exported bank accounts are divided into two parts: the first eight digits and the last eight digits.

If you want to insert the statement above, the text file must exist and have one line: name, bank account 1, bank account 2
Then you can use the following statement to insert
Note that the file name and directory are modified according to your actual situation.

Insert
OpenDataSource ('Microsoft. Jet. oledb.4.0'
, 'Text; HDR = yes; database = c :\'
)... [Aa # TXT]
--, Aa # txt)
--*/
Select name, bank account 1 = left (bank account, 8), bank account 2 = right (bank account, 8)
From
OpenDataSource ('Microsoft. Jet. oledb.4.0'
, 'Excel 5.0; HDR = yes; IMEX = 2; database = c: \ a.xls'
--, Sheet1 $)
)... [Sheet1 $]

 

If you want to directly insert and generate a text file, you need to use BCP

Declare @ SQL varchar (8000), @ tbname varchar (50)

-- First import the Excel table content to a global temporary table
Select @ tbname = '[# temp' + Cast (newid () as varchar (40) + ']'
, @ SQL = 'select name, bank account 1 = left (bank account, 8), bank account 2 = right (bank account, 8)
Into'+ @ Tbname +'From
OpenDataSource (''microsoft. Jet. oledb.4.0''
, ''Excel 5.0; HDR = yes; IMEX = 2; database = c: \ a.xls''
)... [Sheet1 $]'
Exec (@ SQL)

-- Use bcp to export data from a global temporary table to a text file
Set @ SQL = 'bcp"'+ @ Tbname +'"Out" C: \ aa.txt "/s" (local) "/P" "/C'
Exec master .. xp_mongoshell @ SQL

-- Delete a temporary table
Exec ('drop table'+ @ Tbname)

/******************* Guide the entire database **************** *****************************/

Stored procedure implemented with BCP

/*
Stored Procedure for Data Import/Export
You can import/export the entire database or a single table based on different parameters.
Call example:
-- Export call example
---- Export a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region information', 'c: \ zj.txt ', 1
---- Export the entire database
Exec file2table 'zj', '','', 'xzkh _ Sa', 'c: \ docman ', 1

-- Import call example
---- Import a single table
Exec file2table 'zj', '','', 'xzkh _ sa .. region information', 'c: \ zj.txt ', 0
---- Import the entire database
Exec file2table 'zj', '','', 'xzkh _ Sa', 'c: \ docman ', 0

*/
If exists (select 1 from sysobjects where name = 'file2table' and objectproperty (ID, 'isprocedure ') = 1)
Drop procedure file2table
Go
Create procedure file2table
@ Servername varchar (200) -- server name
, @ Username varchar (200) -- user name. If you use the NT authentication method, it is null''
, @ Password varchar (200) -- Password
, @ Tbname varchar (500) -- database. DBO. Table name. If not specified:. DBO. Table Name, all user tables of the database will be exported.
, @ Filename varchar (1000) -- import/export path/file name. If @ tbnameverification indicates that the entire data warehouse is exported, the file name will automatically use table name .txt
, @ Isout bit -- 1 is for export, 0 is for Import
As
Declare @ SQL varchar (8000)

If @ tbname like '%. %. % '-- if the table name is specified, export a single table
begin
set @ SQL = 'bcp '+ @ tbname
+ case when @ isout = 1 then 'out' else' in 'end
+ '"' + @ filename + '"/W'
+'/S '+ @ servername
+ case when isnull (@ username, '') = ''then'' 'else'/u '+ @ username end
+'/P' + isnull (@ password, '')
exec master .. xp_cmdshell @ SQL
end
else
begin -- export the entire database, define the cursor, and retrieve all user tables.
declare @ m_tbname varchar (250)
If right (@ filename, 1) <> '\' set @ filename = @ filename + '\'

Set @ m_tbname = 'Clare # TB cursor for select name from'+ @ Tbname +' .. sysobjectsWhere xtype = ''u '''
Exec (@ m_tbname)
Open # TB
Fetch next from # TB into @ m_tbname
While @ fetch_status = 0
Begin
Set @ SQL = 'bcp'+ @ Tbname +'... '+ @ m_tbname
+ Case when @ isout = 1 then 'out' else' in 'end
+ '"'{@Filename}@m_tbname}'.txt"/W'
+ '/S'+ @ Servername
+ Case when isnull (@ username, '') = ''then'' else'/u'+ @ UsernameEnd
+ '/P' + isnull (@ password ,'')
Exec master .. xp_mongoshell @ SQL
Fetch next from # TB into @ m_tbname
End
Close # TB
Deallocate # TB
End
Go

/************* Oracle **************/
Exec sp_add1_server 'oraclesvr ',
'Oracle 7.3 ',
'Msdaora ',
'Orcldb'
Go

Delete from openquery (mailser, 'select * From yulin ')

Select * From openquery (mailser, 'select * From yulin ')

Update openquery (mailser, 'select * from Yulin where id = 15') set disorder = 555, catago = 888

Insert into openquery (mailser, 'select disorder, catago from Yulin ') values (333,777)