/ * Export to Excel
EXEC MASTER.. cP_CMDSHEC '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 proties = 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 ' "; user ID = admin; password =; extended Properties = Excel 5.0 '' '
Set @S = 'SELECT * from OpenDataSource
' @ S ') ... Sheet1 $ '
EXEC (@S)
* /
SELECT CAST (CAST (Subject No. AS Numeric (10, 2)) AS NVARCHAR (255)) '' Convert alias
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: / Test.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions
/ ********************* EXCEL is derived from remote SQL
INSERT OpenDataSource
'Sqloledb',
'Data Source = Remote IP; User ID = SA; Password = Password'
). Name .dbo. Table name (column name 1, column 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 proties = excel 5.0') ... xactions
/ ** import text file
EXEC MASTER..XP_CMDSHEC 'BCP DBNAME..TABLENAME IN C: /DT.TXT -C -SSERVERNAME -USA-Password'
/ ** Export text file
Exec master..xp_cmdshell 'bcp dbname..tablename out c: /dt.txt -c -ssrvername -usa -ppassword'
or
Exec master..xp_cmdshell 'bcp "Select * from dbname..tablename" Queryout C: /dt.txt -c -ss "exports to txt text, separated by comma
Exec master..xp_cmdshell 'bcp "library name .. Name" D: /TT.TXT "-c -t, -u sa -p password'
Bulk INSERT library name .. 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 Data 4.DBF]')
- * /
- / * DBASE III file
SELECT * FROM
OpenRowSet ('Microsoft.jet.OleDb.4.0'
, 'DBASE III; HDR = NO; IMEX = 2; Database = C: /', 'SELECT * FROM [Customer Information 3.dbf]')
- * /
- / * FoxPro database
Select * from OpenRowSet ('msdasql',
'Driver = Microsoft Visual FoxPro Driver; SourceType = DBF; SourceDB = C: /',
'Select * from [aa.dbf]')
- * /
/ ************** Import 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 a structure (ie, existing) FoxPro list, 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
Description:
SOURCEDB = C: / Specify the folder where the FoxPro table is located
AA.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 SELET * from OpenRowSet ('Microsoft.jet .Oledb.4.0 ',
'x: /a.mdb'; 'admin'; '', a table)
File name is parameter
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 =;') ... Product
******************* Import XML file
Declare @IDOC INT
Declare @doc varchar (1000)
--Sample XML Document
Set @doc = '
Happy Customer.
'
- 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 is guided to txt ********************* ***************** /
I want to use
Select * inTo OpenDataSource (...) from openDataSource (...)
Import an Excel file content into a text file
Suppose there is two columns in Excel, the first list is named, the second list is a very account (16 digits)
And the bank account is exported to the text file and divided by two parts, and the first 8 bits and the last 8 bits were separated.
Zou Jian:
If you want to insert the statement above, the text file must exist, and there is a line: name, bank account 1, bank account 2
Then you can insert with the following statement
Note that the file name and directory are modified 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 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 insert and generate a text file directly, use BCP
Declare @SQL VARCHAR (8000), @ TbName Varchar (50)
- Import 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)
- Then use BCP from global temporary table to text files
Set @ SQL = 'BCP "
' @ TBNAME ' "OUT" C: /AA.TXT "/ S" (local) "/ p" "/ c '
Exec master..xp_cmdshell @SQL
- Delete temporary table
Exec ('Drop Table
' @ TBNAME)
/ ****************************************************************************************************** ****************** / with BCP implementation stored procedures
/ *
Implement data import / export stored procedure
Import / export throughout the database / single table can be implemented according to different parameters
Call example:
- Export call example
---- Export a single table
Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' c: /zj.txt' ,1
---- Export the entire database
Exec file2table 'zj', '', '', 'xzkh_sa', 'C: / DOCMAN', 1
- Imported call example
---- Import a single table
Exec file2table 'zj', '', '', 'xzkh_sa .. Area,' 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) - Username, if using NT authentication mode, it is empty ''
, @ Password Varchar (200) - Password
, @ TBNAME VARCHAR (500) - Database. DBO. Table name, if not specified: .dbo. Download, export all user tables for the database
@ filename varchar (1000) - Import / Export Path / File Name, if the @TBName parameter indicates that the entire database is exported, this parameter is the file storage path, the file name is automatically used .txt
@ isout bit --1 is exported, 0 is imported
AS
Declare @SQL VARCHAR (8000)
If @tbname like '%.%.%' - If the table name is specified, the single table is exported directly.
Begin
Set @ SQL = 'BCP
' @ TBNAME
Case when @ ixout = 1 TEN 'OUT' ELSE 'IN' END
'"
' @ filename ' "/ w '
'/ S
' @ servername
Case When Isnull (@username, '') = '' TEN '' ELSE '/ U
' @ Username End
'/ P' isnull (@password, '')
Exec master..xp_cmdshell @SQL
end
Else
Begin exports the entire database, define a cursor, remove all user tables
Declare @m_tbname varchar (250)
IF Right (@ filename, 1) <> '/' set @ filename = @ filename '/' 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 @ ixout = 1 TEN 'OUT' ELSE 'IN' END
'"
' @ filename @ m_tbname '. txt "/ w '
'/ S
' @ servername
Case When Isnull (@username, '') = '' TEN '' 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)
supplement:
For exporting with BCP, there is no field name.
Export with OpenRowSet and need to be built in advance.
Import in OpenRowSet, except for Access and Excel, non-public data imports are not supported
