SQL Server SQL statement Import and Export)

SQL Server SQL statement Import and Export
SQL statements of the soft SQL Server database are imported and exported, including data imported and exported from other databases and files.
/****** 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 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

/** 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)
* ********************* 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