/******* Export to Excel
EXEC Master. xp_cmdshell ' bcp SettleDB.dbo.shanghu out c:\temp1.xls-c-q-s "Gnetdata/gnetdata"-u "sa"-P "" '
/*********** Importing 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 (@s), varchar (1000)
Set @fn = ' C:\Test.xls '
Set @s = ' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' + @fn + ' "; User id=admin; password=; Extended Properties=excel 5.0 "
Set @s = ' SELECT * from OpenDataSource (' +@s+ ') ... sheet1$ '
EXEC (@s)
*/
SELECT Cast (CAST (account number as numeric (10,2)) as nvarchar (255)) + ' converted alias '
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' C:\Test.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions
/********************** Excel leads to remote SQL
Insert OpenDataSource (
' SQLOLEDB ',
' Data source= remote IP; User Id=sa; Password=password '
). Library 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
/** Importing text files
EXEC Master. xp_cmdshell ' bcp dbname. TableName in C:\DT.txt-c-sservername-usa-ppassword '
/** Exporting text files
EXEC Master. xp_cmdshell ' bcp dbname. TableName out C:\DT.txt-c-sservername-usa-ppassword '
Or
EXEC Master. xp_cmdshell ' bcp ' select * from dbname. TableName "Queryout c:\DT.txt-c-sservername-usa-ppassword"
Export to txt text, separated by commas
EXEC master. xp_cmdshell ' bcp ' Library name: Table name "Out" D:\tt.txt "-c-t,-u sa-p password '
BULK INSERT Library 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] ')
--*/
/************** Importing DBF files ****************/
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 ***************/
Assuming that you want to export data to a generated structure (that is, an existing) FoxPro table, you can use the following SQL statement directly
INSERT INTO OPENROWSET (' MSDASQL ',
' Driver=microsoft Visual FoxPro Driver; SOURCETYPE=DBF; Sourcedb=c:\ ',
' SELECT * FROM [AA. DBF] ')
SELECT * FROM table
Description
Sourcedb=c:\ Specify the directory where the FoxPro table resides
Cal DBF Specifies the file name of the FoxPro table.
/************* Export to access********************/
INSERT INTO OPENROWSET (' Microsoft.Jet.OLEDB.4.0 ',
' X:\A.mdb '; ' Admin '; ', a table) SELECT * FROM database name: b table
/************* Import access********************/
Insert into B table Selet * FROM OPENROWSET (' Microsoft.Jet.OLEDB.4.0 ',
' X:\A.mdb '; ' Admin '; ', table A)
File names are called parameters
DECLARE @fname varchar (20)
Set @fname = ' D:\test.mdb '
EXEC (' SELECT a.* from OpenDataSource (' microsoft.jet.oledb.4.0 ',
' + @fname + '; ' Admin ";" ", topics) as a ')
SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data source= ' F:\northwind.mdb '; Jet oledb:database password=123; User id=admin; password=; ') ... Products
importing 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= "" 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
???????
/**********************excel lead to txt****************************************/
Want to use
SELECT * Into OpenDataSource (...) from OpenDataSource (...)
To import the contents of an Excel file into a text file
If there are two columns in Excel, the first column is the name, the second column is the very line account number (16-bit)
And the bank account number is exported to a text file divided into two parts, the first 8 and the last 8 bits apart.
Kingjian:
If you use the above statement to insert, the text file must exist, and a line: Name, bank account 1, Bank account 2
You can then insert it with the following statement
Note that the file names and folders are changed according to your actual situation.
INSERT INTO
OpenDataSource (' MICROSOFT. JET. oledb.4.0 '
, ' Text; Hdr=yes;database=c:\ '
)... [Aa#txt]
--, aa#txt)
--*/
Select name, bank account number 1=left (bank account, 8), Bank account 2=right (bank account number, 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 insert and generate a text file directly, you will need to use BCP
DECLARE @sql varchar (8000), @tbname varchar (50)
--First import the contents of Excel table into 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)
--then use BCP to export the global temporary table to a text file
Set @sql = ' bcp ' + @tbname + ' "Out" c:\aa.txt "/S" (local) "/P" "/C"
EXEC master. xp_cmdshell @sql
--Delete a temporary table
EXEC (' drop table ' + @tbname)
/******************** Guide the entire database *********************************************/
Stored procedures implemented with BCP
/*
A stored procedure that implements data import/export
Ability to import/export entire database/single table based on different parameters
To invoke the Demo sample:
--Export Call Demo sample
----Export a single table
exec file2table ' zj ', ', ', ' Xzkh_sa. Regional information ', ' C:\zj.txt ', 1
----Export the entire database
exec file2table ' zj ', ', ', ' Xzkh_sa ', ' C:\docman ', 1
--Import Call Demo sample
----Import a single table
exec file2table ' zj ', ', ', ' Xzkh_sa. Regional 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, assuming that the NT authentication method is null '
, @password varchar (200)--Password
, @tbname varchar (500)-database. dbo. Table name, assuming you do not specify:. dbo. Table name, export all user tables of the database
, @filename varchar (1000)--the Import/export path/file name, assuming that the @tbname parameter indicates that the entire database is exported, this is the file storage path, the file name of the active table name. txt
, @isout bit--1 for export, 0 for import
As
DECLARE @sql varchar (8000)
If @tbname like '%.%.% '--If a table name is specified, export a single table directly
Begin
Set @sql = ' bcp ' + @tbname
+case when @isout =1 and then ' off ' 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--Exports the entire database, defines the cursor, and takes out all the user tables
DECLARE @m_tbname varchar (250)
If Right (@filename, 1) <> ' \ ' set @[email protected]+ ' \ '
Set @m_tbname = ' Declare #tb cursor for select name from ' + @tbname + ' ... sysobjects where 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 and then ' off ' else ' in ' End
+ ' ' + @filename + @m_tbname + '. txt '/w '
+ '/S ' + @servername
+case when IsNull (@username, ") = ' Then ' Else ' u ' + @username end
+ '/P ' +isnull (@password, ')
EXEC master. xp_cmdshell @sql
FETCH NEXT from #tb into @m_tbname
End
Close #tb
Deallocate #tb
End
Go
/************* Oracle **************/
EXEC sp_addlinkedserver ' 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)
Add:
For export with bcp, there is no field name.
To export with OPENROWSET, you need to build a table beforehand.
Import with OpenRowset, non-native data import is not supported except Access and Excel
