/ ******* Export to Excelexec master..xp_cmdshell 'bcp settledb.dbo.shanghu out c: /temp1.xls -c -q -s "gnetdata / gnetdata" -u "sa" -p ""
/ *********** Import Excelselect * 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), @fn = 'c: /test.xls'Set @S =' '' Microsoft.jet.Oledb.4.0 ',' ' Data Source = "' @ fn '"; user ID = admin; password =; extended profr 5.0 '' set @s = 'SELECT * OPENDATASOURCE (' @ S ') ... Sheet1 $' EXEC (@S) * /
SELECT CAST (CAST (Subject No. 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 is derived to remote SQLInsert OpenDataSource ('sqloledb', 'data source = remote IP; user ID = sa; password = password' ). Tin name .dbo. Table name (column name 1, column name 2) SELECT column name 1, column name 2From OpenDataSource ('Microsoft.jet.OleDb.4.0', 'data source = "c: /test.xls" User ID = admin; password =; extended Properties = Excel 5.0 ') ... xactions
/ ** Import text files EXEC MASTER..XP_CMDSHELL 'BCP DBNAME..TABLENAME IN C: /DT.TXT -C -SSERVERNAME -USA-PSSWORD'
/ ** Export text file EXEC MASTER..XP_CMDSHEC 'BCP DBNAME..TABLENAME OUT C: /DT.TXT -C -SSERVERNAME -USA -PASSWORD' or EXEC MASTER..XP_CMDSHELL 'BCP "Select * from dbname..tablename" Queryout c: /dt.txt -c -sservername -usa -ppassword 'Exports to TXT text, separate the exec master..xp_cmdshell' bcp "in comma" 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: / Specifies the folder AA.DBF of the FoxPro table to specify the file name of the FoxPro table.
/ ************* Export to Access ******************* / INSERT INTO OPENROWSET ('Microsoft.jet.OleDb.4.0 ',' X: /a.mdb ';' admin ';' ', 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'; ' ', A table)
File named 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 INTDECLARE @doc varchar (1000) - sample XML DocumentSet @doc = '
???????
/ ********************* EXCEL is guided to txt ********************* ***************** / want to use Select * Into OpenDataSource (...) from openDataSource to 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 queue (16-bit) and the bank account is exported to the text file, and the first 8 bits and the post-8 bits are 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 can then use the following statement to pay attention file name and directory according to your actual situation modify.
INSERT INTOOPENDATASOURCE ('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)
- First import the Excel table content 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 from global temporary table to text file set @ SQL = 'bcp "' @ TBNAME " OUT "C: /AA.TXT" / S "(local)" / p "" / c'exec Master..xp_cmdshell @sql
- Delete Temporary Table EXEC ('Drop Table' @ TBNAME)
/ ****************************************************************************************************** ****************** /
Stored procedure implemented with BCP
/ * Implementing data import / export stored procedures can implement import / export of the entire database / single table call example: - Export Call Example ---- Export Call Example ---- Export Call Example - Export Single Table EXEC File2Table 'ZJ', '', ' ',' xzkh_sa .. area, '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 .. 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 File2Tablegocreate procedure File2Table @ servername varchar (200) - the server name, @ username varchar (200) - - Username, if you use NT authentication mode, it is empty ', @ password varchar (200) - password, @ TBNAME VARCHAR (500) - Database .dbo. Table name, if not specified: .dbo. Table name , Export all user tables of the database, @ filename varchar (1000) - Import / Export Path / File Name, if the @TBName parameter indicates to export the entire database, this parameter is the file storage path, the file name is automatically used. TXT, @ isout bit --1 is exported, 0 is imported as an asdeclare @SQL varchar (8000) if @tbname like '%.%.%' - If the table name is specified, then export a single table begin set @ SQL = 'bcp' @ TBNAME CASE WHEN @ ISOUT = 1 TEN 'OUT' ELSE 'IN' END '"' @ filename '" / w' '/ s' @ ServerName Case When ISNULL (@username, '') = '' THEN 'ELSE' / U ' @ username end ' / p ' isnull (@password,' ') exec master..xp_cmdshell @sqlendelsebegin - Export the entire database, define a cursor, remove all User table 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 @ iSout = 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_CMDSHEC MASTER..XP_CMDSHEC @SQL fetch next from #tb @m_tbname endclose #tb deallocate #TB endgo / ************ Oracle ************ ** / exec sp_addlinkedServer 'ORACLESVR', 'Oracle 7.3', 'MSDara', '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)
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
Excerpted from: http://blog.9cbs.net/txlicenhe/archive/2003/12/15/21031.aspx
